3

On my front end, I am using google maps JS api. I am adding markers, the google maps event gives me the lat/lng for where I add the marker.

I'm adding that marker into my postgres database:

INSERT INTO markers(created_by, title, description, lat, lng, geography)\
values($1, $2, $3, $4::decimal, $5::decimal, ST_SetSRID(ST_MakePoint($5::decimal, $4::decimal), $6))

Now, I have the marker added into my database and from the front end I can show the user which markers are within a mile or whatever of his current location.

However, I would like to start showing results by zip code or the city associated with the user's current location.

For example, let's say I am adding this latLng to my database: 37.555880, -122.325580. In my table I'm just going to add these points and the geography. But, this is in the city of San Mateo, CA, and the zip code is 94402....

How can I add this information to my database given just the lat and long?

If it isn't possible, then I will look into using the reverse geocoding API from Google going forward for future points, so I can get this information from the front end itself and insert it.

2

Yes, it is possible. a source that'll refer too A generic solution to PostGIS nearest neighbor provides an approach

"I have come up with a faster and more generic solution to calculating nearest neighbors"

For example, the author provides this query to answer, "For each street segment in zip 02109 list the 2 nearest fire stations and the distance in meters from each fire station. Order results by street name, street id and distance from nearest fire station "

SELECT g1.gid as gid_ref, g1.street, g1.fraddl, g1.toaddl, f.name, g1.nn_dist as dist_meters, g1.nn_gid
FROM (SELECT b.,
(pgis_fn_nn(b.the_geom, 1000000, 2,1000,'fire_stations', 'true', 'gid', 'the_geom')).

FROM (SELECT * FROM census2000tiger_arc where zipl = '02109') b) As g1, fire_stations f
WHERE f.gid = g1.nn_gid
ORDER BY g1.street, g1.fraddl, g1.gid, g1.nn_dist

Also, as an additional resource that you may find useful, US Zip Codes from 2013 Government Data

  • Thanks! That's so awesome. In my case, since I want some really up to date data, is it better that in the future that I just use the google geocoder from my front end to send the necessary data over? – user91463 Feb 14 '17 at 1:23
  • I would say that's a good approach, IMO. – whyzar Feb 14 '17 at 1:26

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.