0

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]}

closed as unclear what you're asking by Andriy M, dezso, Philᵀᴹ, Marcello Miorelli, Erik Nov 23 '16 at 16:06

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 6
    Do you just want coordinates or whole rows with distinct coordinates? In the later case you need to define which row from a set of peers. Also, please always provide table definition and Postgres version. – Erwin Brandstetter Nov 21 '16 at 3:21
  • So then just use that column like any other column to do exactly what you want. You're serializing that column to geojson, and you're not telling us why. It doesn't make sense. If I ask you how to How do I select distinct json columns?, but I'm generating the json columns from integers the question is why are you doing that? Why not just use the integers? You can just do SELECT DISTINCT ON (startstationgeom), * FROM tbl or whatever. Or see which points have duplicates, SELECT startstationgeom, count(*) FROM tbl GROUP BY startstationgeom HAVING count(startstationgeom) > 1; – Evan Carroll Nov 23 '16 at 22:26
-1

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.

-2

simple form of query is:

select
    max(id),
    jtext->>'coordinates' as coordinates

from t_json
GROUP BY coordinates

more information about JSON - https://www.postgresql.org/docs/9.5/static/functions-json.html

just other form of same query:

select
    distinct
    jtext #>> '{coordinates,0}' as lat,
    jtext #>> '{coordinates,1}' as lon
from t_json

tyson and j_text - it name of table and json column in my example

first form allow return other columns, not only coordinates in distinct query

  • Hey man, thanks for your response. I get HINT: "co" <-- parse error at position 2 within geometry any suggestions? – Micheal C. Nov 21 '16 at 12:04
  • PostGIS has a GeomJSON parser built in. I would use that, and let C do the lifting. – Evan Carroll Nov 23 '16 at 7:52
  • Welcome, and If it not coordinates, You also will look for other extensions ;) – a_vlad Nov 23 '16 at 8:11
  • @MichealC. - this is error from PostGIS, so need check - what the problem, or with Your query or with Your data – a_vlad Nov 23 '16 at 11:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.