CODEWITHSUNDEEP
postgresqlgeospatialpostgis
opticon
opticon

Reputation: 3614

PostGIS: ST_Contains with a lat/long pair and GeoJSON text

Heyo,

I have a table that contains latitude/longitude columns, and another that contains GeoJSON in the form of a string.

I'd like to join the tables on the points from one table being within the polygons in the other. I've got something like this:

select s.id, s.name, s.city, s.province, cz."geoJson" from "TheZones" cz
   inner join "OtherTable" s 
   on st_contains(ST_GeomFromGeoJSON(cz."geoJson"), st_point(s.latitude, s.longitude));

This is spitting out the error: ERROR: contains: Operation on mixed SRID geometries (Polygon, 4326) != (Point, 0)

No matter how I cast or transform these I can't seem to get it to work. Thoughts?

Upvotes: 0

Views: 986

Answers (1)

Ian Turton
Ian Turton

Reputation: 10976

As the error says, your 2nd point has no projection set. You can set the SRID by using ST_SetSRID so your where clause becomes:

on st_contains(ST_GeomFromGeoJSON(cz."geoJson"),
   st_setsrid(st_point( s.longitude, s.latitude),4326))

Note: you also need to use longitude, latitude when using 4326 with PostGIS.

Upvotes: 4

Related Questions