1

I am using Postgres with postGIS extension. I just installed everything a while ago.

I'm looking at the documentation (it is really difficult for beginners in my opinion - is there any other tutorial or something you recommend?) and it seems I have to create a point from my lat and long, and that in a geospatial database it is (x,y) so I have to flip my lat and long to long, lat.

So, I'm creating markers in my front end application, but I'm confused as to how I can store them in my database.

Here's an example of the marker data that my web Api (using node and a postgres client) is sending over to Postgres (I am able to insert it into my table, so I have some test data):

{
  "lat": 40.7128,
  "lng": 74.0059,
  "title": "Big Apple",
  "description": "New York City, NY, USA.  The best city in the world!",
  "createdBy": 33823,
  "type": 4
}

My goal with using postGIS is to be able to do something like this:

I am using my app and I have my current location. Find stuff that's within 1 mile of me.

Is there any guidance you can provide to me on how to get started towards that?

From the documentation, I need to create a point. But then it delves into using a type of coordinate system to use. At this point I have no idea what anything means. I am trying to put together a proof of concept for an idea I've had for a while, but this is the biggest roadblock I've hit.

  • 1
    Perhaps a code snippet of what you've tried so far would be helpful. – Jesse McMillan Jan 29 '17 at 20:24
  • this location is in the Western hemisphere, where longitudes are negative – Mike T Jan 30 '17 at 3:59
1

Coordinate reference systems have to do with map projection. In the GIS world there are many reference systems and postgis needs to know what you're using.

Since your coordinates are in lat/lon you can use wgs84 which has an epsg reference number of 4324. Just pass 4324 as the final argument in your geometry insert.

The column definition could be something like:

St_geomfromtext( 'POINT:(' || lon || ' ' || lat || ')' , 4324)

Then once you've populated your database with points and their attributes you can use st_dwithin to get the points within the user's area:

Select... From... Where st_dwithin(my.geometry, poi.geometry, 1609)
  • THanks Jesse! Just a quick question. Can you please explain the 1609 value in the Select query? I understand now that 4324 would be my reference system since I am using lat/long. However, I am confused about the 1609. Thank you! – a person Feb 1 '17 at 8:56
  • 1609 meters are approximately one mile. – Jesse McMillan Feb 1 '17 at 17:05
  • Ah ok. So I had to change my column to geography and now I can query by meters. With geometry it uses radians according to the documentation perhaps that is why it was not working. I am not good at math so I will hope this is suitable :D – a person Feb 1 '17 at 19:43

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.