1

I have an address in lat long format. When i pull up the address on Google Maps, the marker visually looks like it falls inside the map polygon boundary I have.

However, when I use the ST_INTERSECTS query to check the lat lon against the polygon boundary, I get a false. This is the query I am running

select st_intersects(st_geomfromtext('POINT(lon lat)', 3785), st_geomfromtext(polygon_coordinates));

I have checked the SRID of both the geoms to make sure they match and also tried ST_WITHIN and ST_CONTAINS. No luck.

Can someone throw some light on what I am missing here?

MULTIPOLYGON Boundarymarker from google maps

  • 1
    Please show what values you use as lon and lat. – user30184 Mar 14 '17 at 19:10
  • latitude: 45.5362 longitude: -122.591 – asimsf Mar 14 '17 at 21:28
  • 1
    Then you should make point as st_geomfromtext('POINT(lon lat)', 4326 – user30184 Mar 14 '17 at 21:51
  • 1
    SRID 3785 does not accept lon,lat coordinates, it accepts x,y in meters. You must project a lot,lat in WGS84 to Web Mercator X,Y (see ST_Transform) – Vince Mar 14 '17 at 23:24
3

As is often the case, the software gave the correct answer. You asked if a point off the coast of Africa (within 131 meters of the intersection of the Equator and Prime Meridian) was inside Portland, Oregon, and it said no.

It is not enough to assert equivalent SRIDs -- you must actually provide coordinates in the SRID you are asserting. Your mistake was providing:

st_geomfromtext('POINT(-122.591 45.5362)', 3785)

when what you really had was:

st_geomfromtext('POINT(-122.591 45.5362)', 4326)

Obviously, this would produce an SRID conflict during comparison, but there's no reason you can't reproject the WGS84 data into Web Mercator, with ST_Transform:

st_transform(st_geomfromtext('POINT(-122.591 45.5362)', 4326), 3785)

I would warn that the coordinate precision you provided is inadequate for point-in-polygon operations at the resolution of your map. Three places of precision in decimal degrees is +/-111 meters, which could snap to several streets in the neighborhood, and in fact is located in a different polygon: enter image description here

For meter-ish precision (which is a tenth of conventional accuracy), be sure to use at least five decimal places in decimal degrees coordinates.

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.