'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?

postgresqlgeospatialpostgis


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Ian Turton

Related Questions