[GIS] PostGIS convert GeoJSON to geography

geography-data-typegeojsonpostgis

While there are functions to convert geography to GeoJSON:

text ST_AsGeoJSON(geography geog, integer maxdecimaldigits=15, integer options=0);

I do not see any way to go the opposite direction (GeoJSON to geography) only to geometry.
http://postgis.net/docs/reference.html#Geometry_Constructors

Is this on the roadmap?

The data in the database has geography polygons or multi-polygons to describe each item in the DB. The query is coming from lealfet.js, which allows me to easily get the shape in GeoJSON.

What I am doing is just a simple ST_Intersects() to check for overlap and return. Yes, I have a hack to convert this to EWKT right now, but I was wondering if it was on the roadmap to support this in a future version of PostGIS, or where one adds it as a wish list (since symmetry is a good thing, and it seems a shame that only geometry is supported in that direction).

Best Answer

Why don't you just do:

SELECT ST_GeomFromGeoJSON(geojsongoeshere)::geography;

Select the polygons that cross the anti-meridian, cast to geom and shift to 0-360, then split them. Select the split polys with any longitude > 180, and shift back 360 deg

Create table beams_fixed_dump4 as Select ST_Translate(foo2.thegeom, -360, 0) as newgeom from
(Select (ST_DUMP(foo.geom)).geom as thegeom from 
(Select ST_Split(shift_lon.geom, line2) as geom 
    from ST_SetSRID(ST_MakeLine(ST_MakePoint(180, -90),ST_MakePoint(180, 90)), 4326) As line2, 
    (Select ST_ShiftLongitude(geog::geometry) as geom from 
        (SELECT ST_Intersects(poly_coverage.geog, line) as inter, poly_coverage.* from poly_coverage, ST_GeogFromText('LINESTRING(180 -90, 180 0, 180 90)') As line) as tmp where inter = TRUE) as shift_lon) as foo) as foo2 where st_xmax(foo2.thegeom) > 180
Union all

Select the polygon that cross the anti-meridian, cast to geom and shift to 0-360, then split them. Select the split polys with all longitude <= 180.

Select newgeom from 
(Select (ST_DUMP(foo.geom)).geom as newgeom from 
(Select ST_Split(shift_lon.geom, line2) as geom from 
    ST_SetSRID(ST_MakeLine(ST_MakePoint(180, -90),ST_MakePoint(180, 90)), 4326) As line2, 
    (Select ST_ShiftLongitude(geog::geometry) as geom from 
        (SELECT ST_Intersects(poly_coverage.geog, line) as inter, poly_coverage.* 
            from poly_coverage, ST_GeogFromText('LINESTRING(180 -90, 180 0, 180 90)') As line) as tmp where inter = TRUE) as shift_lon) as foo)as foo2 where st_xmax(newgeom) <= 180
Union all

Select the polygons that don't cross the anti-meridian and cast to geom.

SELECT foo.geog::geometry from 
(Select ST_Intersects(poly_coverage.geog, line) as inter, poly_coverage.* from 
poly_coverage, ST_GeogFromText('LINESTRING(180 -90, 180 0, 180 90)') As line) as foo where inter = FALSE;

Best Answer

Related Solutions

[GIS] How to convert PostGIS geography to geometry and split polygons that cross the antimeridian

Select the polygons that cross the anti-meridian, cast to geom and shift to 0-360, then split them. Select the split polys with any longitude > 180, and shift back 360 deg

Select the polygon that cross the anti-meridian, cast to geom and shift to 0-360, then split them. Select the split polys with all longitude <= 180.

Select the polygons that don't cross the anti-meridian and cast to geom.

Related Question