2

I have a shapefile created from ArcMap that contains about 400 geometry's. I've added this shapefile into PostGIS using the shp2pgsql tool. I am trying to use the ST_Intersects function to determine if a given point, using Lat and Long, is within one of the 400 geometry's. I have a point that I know to be within one of the shapes, but ST_Intersects is returning false for this. The record where cocnum equals "MA-500" contains the point Lat: 42.3581, Long: -71.0636.

                    SELECT 
                        cocname,
                        cocnum, 
                        geom,           
                        st_intersects(geom, point(-71.0636,42.3581)::geometry)
                    FROM 
                        public.coc
                    WHERE cocnum like 'MA-500'

However, I get the following result:

Query Result

When I create a KML of the geometry using st_asKml(geom) and then insert that into google maps, along with the point google.maps.LatLng(42.3581, -71.0636) I get the following result: map

Any ideas on why ST_Intersects is returning false for this?

Any guidance given is greatly appreciated, Thanks!

Ed

  • Is your data in WGS84 or something similar (lat/lon) or some other projection? – Evil Genius Jan 17 '14 at 18:12
2

You need to project your point into your polygon's CRS. You can use ST_Transform to do this.

                    SELECT 
                        cocname,
                        cocnum, 
                        geom,           
                        st_intersects(geom,
                            ST_Transform(
                                  ST_GeomFromText('point(-71.0636 42.3581)',4326), 3857))
                    FROM 
                        public.coc
                    WHERE cocnum like 'MA-500'

Specifically you're not intersecting because your polygons projection is in meters, so without re-projecting -71.06, 42.35 in meters is a point not far from the equator and prime meridian.

Edit: Sorry, I forgot a ')'.

  • Thank you so much, that did the trick. If you don't mind me asking, where does 4326 come from. I understand 3857 is the SRID of the shapefile I'm using, so is 4326 the SRID of lat/long points? Also, I used this same process with other Shapefiles, and I did not need to use ST_Transform. They were based on the GCS_North_American_1983. Is the different Geographic Coordinate System the reason for needing to use ST_Transform or not? – ekbarber Jan 17 '14 at 20:19
  • Yes, 4326 is the EPSG code for WGS84 lat/long. That's the system that GPS uses, and most of the time when you see coordinates in lat / long they're WGS84 coordinates. Another common one is NAD83 lat/long which I think is EPGS code 4269. You'll see it if you use TIGER data directly from the Census folks. The points you didn't need to use ST_Transform were in the NAD83 lat / long, which is very similar to WGS84. So that's why it worked without re-projecting your points. – HeyOverThere Jan 17 '14 at 20:25
  • Ok great, that makes sense. Thanks again for all your help! – ekbarber Jan 17 '14 at 20:55
  • I'm surprised you didn't get an SRID mismatch error. Perhaps you loaded your polygon data without an SRID. – Paul Ramsey Jan 18 '14 at 13:32
  • Paul, that's exactly what I did. I'm fairly new to GIS, and didn't know what the SRID was, and saw in the shp2pgsql documentation that SRID was optional, I didn't include it. – ekbarber Jan 21 '14 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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