[GIS] How to insert Google Maps API Lat/Long into PostgreSQL Postgis GEOMETRY(GEOMETRY, 4326)

postgis

I have very little knowledge and understanding when it comes to GIS (despite extensive experience with Google Maps API).

I've recently installed PostGIS (v2.1.5) with my PostgreSQL (v9.4) instance in order to begin storing and working with GIS data. I'm currently using Google Maps API v3's DrawingManager to draw polygons, circles, rectangles and markers which need to be stored in the database, and later retrieved to display the polygons again on Google Maps; These shapes will also be used for various aggregate queries.

I currently have two tables. The one is called "locations" which will store the polygons, circles and rectangles created with Google Maps' DrawingManager. The second, called "rawdata" will contain a set of records with a POINT (lat, long) inside of it.

First and foremost, I do not entirely understand the concept of projections and how they work, but, based on what I've read online is that calculations and accuracy may vary, significantly. I understand that Google Maps works with the Web Mercator projection which I believe is officially known as the EPSG:3857 projection (or CRS), while the preferred method for storing and working with geometry() shapes in PostGIS is in the EPSG:4326. As such and in reference to my create table statements below, I have chosen to store my geometry data in EPSG:4326 in the tables.

An easier method would to be store my geometry in the newer geographic format, but I do not wish to impose those performance and lack of functionality drawbacks as I will be working with a significant amount of data (hundreds of thousands, if not millions of records).

How should I store (and convert if applicable) the LAT/LONG coordinates which Google Maps supplies me with, assuming that my "geom" column is in the EPSG:4326 CRS? I found the simplest form was to use ST_GeomFromGeoJSON, but do I not know if this is the correct way or not.

INSERT INTO locations (label, geom) VALUES
(
    'Area Label',
    ST_GeomFromGeoJSON('{"type":"Polygon","crs":{"type":"name","properties":{"name":"EPSG:4326"}},"coordinates":[[[28.208620548248,-25.851267945548],[28.206732273102,-25.853353448277],[28.207848072052,-25.856597490521],[28.214156627655,-25.853469308462]]]}')
)

Here's where my confusion with the projections come in though. The lat/long coordinates I am passing through are retrieve from Google Maps API (which as I understand is in EPSG:3857 CRS), but I am supposedly storing this in the column as EPSG:4326… will this work?

Here's the table structures:

CREATE TABLE locations
(
    id BIGSERIAL,
    label VARCHAR(255) NOT NULL,
    geom geometry(geometry, 4326)
)  WITH (oids = false);

CREATE TABLE rawdata
(
    id BIGSERIAL,
    geom geometry(point, 4326)
);

ALTER TABLE locations_areas ALTER COLUMN geom TYPE geometry(geometry, 4326) USING ST_Transform(geom, 4326);

Best Answer

There are several issues mixed into your question. First, PostGIS can store geometries in the geometry column in any of thousands of CRS. The "Spherical Mercator" 3857 is a projected CRS, popular in web mapping. And the well known WGS84, epsg 4326 is an unprojected (geographic, long/lat) CRS.

As for importing geojson to PostGIS, have you tried the straight forward ogr2ogr utility. That's probably the easiest if you're not yet familiar with SQL and spatial databases. Have a look at this post. The utility will create PostGIS tables with the geom column in the CRS of the geojson data.

Related Question