PostGIS intersection troubleshooting

2
votes

I just started using PostGIS & Postgresql and everything is running smoothly for the most part. When I try to find which MULTIPOLYGON s a POINT lies in I'm getting stuck. I have two separate points that I am certain lie inside one and only one shape that is of MULTIPOLYGON data type in my database. They are not the same points and they are in different formats.

Example 1, I'm not sure what format it is but the query returns true value like I expected (note, I found this value by loading the dataset into QGIS and hovering over a point inside).

In the second example, I geocoded an address that lies inside the shape I'm looking at. However, a false value is returned as a result of the query.

I used shp2pgsql to load the data into my database directly from a shape file. The shape file's SRID is 4269 (I've tried running the queries below while specifying the SRID during the GeomFromText call but the results are the same).

What is the difference between the two coordinate sets? What do I need to do so that I can perform an intersection test using POINTS that use lat/lon values?

1.) SELECT ST_Intersects((select the_geom from wardstable where gid=37), ST_GeomFromText('POINT(1172539 1924462)'));

2.) SELECT ST_Intersects((select the_geom from wardstable where gid=37), ST_GeomFromText('POINT(-87.6547884 41.96367)'));

Thanks!

1
postgresqlgispostgis

1 Answers

1
votes

Both the Multipolygons and the Points dataset should be in the same projection (SRID) when performing a spatial operation like ST_Intersects. In your second example the point's coordinates are in lat/lon (4326). You should transform them to 4269 using ST_Transform:

SELECT ST_Intersects((select the_geom from wardstable where gid=37), ST_Transform(ST_GeomFromText('POINT(-87.6547884 41.96367)',4326),4269));

Edit: I missed the SRID parameter in ST_GeomFromText.