Getting latLng from google maps api to store that in PostGIS?

Question

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.

Answer 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)