How to get asGeoJSON() to return in degrees instead of metric coordinates?

[S Adi W]

Dear Spatialite list,

I have a Spatialite database created using QSpatialite in QGIS with a table that contains multipolygon geometries in 4326 CRS (as shown in the srid column when clicking Show Spatial Metadata in Spatialite GUI 2.0.0 devel on Windows 7). My goal is to populate a json text column in the same table by running "UPDATE mytable set json = asGeoJSON(Geometry);" in Spatialite GUI. The query runs fine however the resulting geojson seems to be in metric coordinate like the following when querying the json column:

{

• "type": "FeatureCollection",
• "crs": {
  • "type": "name",
  • "properties": {
    • "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },
• "features": [
  • {
    • "type": "Feature",
    • "properties": {
      • "id": 1,
      • "objectid": 1,
      • "prov": "PROV 1",
      • "id_prov": 11,
      • "shape_leng": 2973855.722,
      • "shape_area": 57133640150.5
      },
    • "geometry": {
      • "type": "MultiPolygon",
      • "coordinates": [
        • [
          • [
            • [
              • 10842196.924,
              • 225437.2053
              ],
            • [
              • 10842059.7979,
              • 225391.9449
              ],
            • [
              • 10841885.771,
              • 225443.8408
              ], and so on...

Meanwhile this is how it is returned by QGIS when saving the shapefile as geojson in 4326 CRS:

{

• "type": "FeatureCollection",
• "crs": {
  • "type": "name",
  • "properties": {
    • "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },
• "features": [
  • {
    • "type": "Feature",
    • "properties": {
      • "OBJECTID": 1,
      • "PROVINSI": "PROV 1",
      • "ID_PROV": 11.0,
      • "Shape_Leng": 2973855.722,
      • "Shape_Area": 57133640150.5
      },
    • "geometry": {
      • "type": "MultiPolygon",
      • "coordinates": [
        • [
          • [
            • [
              • 97.397112102628597,
              • 2.038349409770451
              ],
            • [
              • 97.395880277913776,
              • 2.037940350939831
              ], and so on...

Is there something missing from my query? Thanks and apologies for the newbie question.

Best regards,

S Adi W

[mj10777]

SELECT ST_Transform(MakePoint(97.397112102628597,2.038349409770451,4326),3395)

SRID=3395;POINT(10842196.92399999 225437.2052969851)

 For some reason the first is in WGS 84 / World Mercator EPSG:3395.

So one solution is the following:

UPDATE mytable set json = asGeoJSON(ST_Transform(SetSRID(Geometry,3395),4326));

Is it correct that the position is west of northern island of Indonesia?

Mark

[S Adi W]

Hi Mark,

Thanks a lot! Looks like your update query did the trick. Indeed the multipolygon geometries are situated in the northwestern tip of Sumatra. Now here is the output of the json column:

{

• "type": "FeatureCollection",
• "crs": {
  • "type": "name",
  • "properties": {
    • "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
    }
  },

• "features": [
  • {
    • "type": "Feature",
    • "properties": {
      • "id": 1,
      • "objectid": 1,
      • "provinsi": "PROV 1",

• • • • "id_prov": 11,
      • "shape_leng": 2973855.722,
      • "shape_area": 57133640150.5

• • • },
    • "geometry": {
      • "type": "MultiPolygon",
      • "coordinates": [
        • [
          • [
            • [
              • 97.397112102629,
              • 2.0383494097705
              ],
            • [
              • 97.395880277914,
              • 2.0379403509398
              ],
            • [
              • 97.394316967673,
              • 2.0384093807267
              ], and so on...
              

I wonder how the CRS got to become EPSG 3395 in the first place? I made sure to "Set Layer CRS" to WGS 84 in QGIS and in QSpatialite when importing the QGIS layer I also made sure to set desination SRID to 4326 as confirmed by srid column in the resulting table. I tried loading the shapefile via Spatialite GUI (setting SRID field to 4326) and it is the same issue too.

Best regards,

S Adi W

[mj10777]

To that I cannot say anything.

However, as a final solution you should look at your 'Geometry' Field. 

In spatialite_gui look at the first Geometry.: Blob explore/EWKT

- does it start with  'SRID=4326'?

- and does the first point start with something like '97.397112102628597,2.038349409770451' or '10842196.92399999 225437.2052969851'?

If the second, then you should update the Geometry field: 

UPDATE mytable set Geometry = ST_Transform(SetSRID(Geometry,3395),4326);

Then all you would need is:

UPDATE mytable set json = asGeoJSON(Geometry);

In this case it is important that the Geometry field is correct.

Mark

[S Adi W]

Mark,

After checking in Blob Explore/EWKT, all of the points were like the latter case.

Much appreciated for the insight.