How do I select distinct json columns? I have over 10,000 geojson rows in a table. How do I go about selecting only distinct coordinates?

The data was imported from a csv file into PostgreSQL 9.5. The columns in the table are:


Trip Duration (seconds)
Start Time and Date
Stop Time and Date
Start Station Name
End Station Name
Station ID
Station Lat/Long


The imported contains I took the latitude and longitude columns and turned it into a geojson object: 

update h set thestartgeom =  st_asgeojson(startstationgeom);


The column thestartgeom in the table is

  thestartgeom text; 


The database contains 10,000+ lat/lng and other records. Some of the coordinates are duplicated. I am trying to eliminate all duplicates.

startstationgeom is a geometry(POINT,4326).

Here is the geojson column:

                    thestartgeom                            
---------------------------------------------------------------------

{"type":"Point","coordinates":[40.6882247924805,-73.9793853759766]}
{"type":"Point","coordinates":[40.7329139709473,-74.0071105957031]}
{"type":"Point","coordinates":[40.7429084777832,-73.9770584106445]}
{"type":"Point","coordinates":[40.7160568237305,-73.9919052124023]}
{"type":"Point","coordinates":[40.7497177124023,-74.0029525756836]}
{"type":"Point","coordinates":[40.7349281311035,-73.9920043945312]}
{"type":"Point","coordinates":[40.7281455993652,-73.9902114868164]}
{"type":"Point","coordinates":[40.691650390625,-73.9999771118164]}
{"type":"Point","coordinates":[40.7501983642578,-73.9909286499023]}
{"type":"Point","coordinates":[40.7503814697266,-73.9833908081055]}
You'll want PostGIS's ST_GeomFromGeoJSON().

SELECT DISTINCT ST_GeomFromGeoJSON(geojson)
FROM tbl;


Should work to get the points. However may find this is faster... I'd be interested in seeing a benchmark. ST_Union will eliminate duplicate points and geometries too.

SELECT ST_Union(ST_GeomFromGeoJSON(geojson))
FROM tbl;


Update

Is it faster, what advantages and disadvantages does this have?


DISTINCT requires a SORT. faster to sort floats than strings.
My ST_Mem_Size(ST_GeomFromGeoJSON(geojson)) shows 32 bytes (two floats).
Not sure how much space the raw JSON takes, likely more than 32 bytes.
Likely have to have these as ::geography at some time.
Processing the JSON and creating points is a one time cost but it may be significant.
ST_GeomFromJSON supports all geometry objects. Including MultiPoints.


Update2

If you already got the type as startstationgeom, just do

SELECT ST_AsGeoJSON(ST_Union(startstationgeom)) FROM table


That will output only unique geometries.