2

I am mostly relying on What Spatial Reference System do I store Google Map's Lat/Lng in

My web application takes user addresses, queries the Google geocoding API for coordinates, and then stores those coordinates in a postgresql postgis-enabled database. To do this I run the following insert command:

$query = "INSERT INTO userloc (email, latitude, longitude) VALUES($1, $2, $3)";
$rs = pg_query_params($con, $query, array($emailN, $latitude, $longitude));

followed by a command within postgresql:

UPDATE userloc SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);

I am only storing data for a few hours (far less than 30 days), so my understanding is I am not violating google TOS.

What I did is based on the post I referenced saying that google geocoding already comes in 4326 format.

Once I have a collection of these points and I want to find all points within a certain range of a particular longitude, latitude pair, what query do I run?

I have tried the following:

$query = "SELECT * FROM userloc WHERE ST_DWithin(the_geom, 'POINT(".$longitude." ".$latitude.")', 100.0)";
$rs = pg_query($con, $query);

When I run this, I get the following error:

pg_query(): Query failed: ERROR: Operation on two GEOMETRIES with different SRIDs in /var/www/html/signup.php on line

If I try running this instead, using ST_SetSRID:

$query = "SELECT id FROM userloc WHERE ST_DWithin(the_geom, ST_SetSRID(ST_MakePoint(".$longitude.", ".$latitude."),4326), 100.0)";
$rs = pg_query($con, $query);

I don't get an error, but the search doesn't make sense. Or it might make sense if it I knew more about geometric units because the distance parameter (100 above) clearly doesn't correspond to normal distance. If I make it very small it does cut to the nearest (a cluster in NY), but if I make it just slightly larger it starts including points in Chicago.

I thought this probably had something to do with geography vs. geometry. I am interested in geographic searches, so then I thought I should convert to geography like so:

$query = "SELECT id FROM userloc WHERE ST_DWithin(the_geom::geography, ST_SetSRID(ST_MakePoint(".$longitude.", ".$latitude."),4326)::geography, 100000.0)";
$rs = pg_query($con, $query);

This really seems to do the trick (can distinguish nearness within cities), but the units are still baffling. I thought the default units were meters, but I had to enter the # above, 1 with 5 0's to get all the points in Chicago to be included in a Chicago-oriented search, so I don't think it is meters.

So two questions.

Can someone confirm that what I have done makes sense?

I am sorry I am so new to gis in general, so I am struggling to get a hold of all the different systems of measurements, data storage.

Second, can someone tell me what the distance is in my last command or how I can find out?

I need to be able to set a specific distance.

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Sunnyside
  • 155
  • 5

1 Answers1

1

It looks fine and the distance is always measured in the units the SRID used defines. For 4326 that would be in angular units. Unless you use geographies like you found out, that is. Read more in the official docs here.

Why 100 km is needed for Chicago is up to you and your data. It doesn't sound that much for a huge city and in fact if I check Wikipedia, the area of the metropolitan part is just a bit below that of a circle with a 100 km radius.

lynxlynxlynx
  • 4,247
  • 3
  • 29
  • 45