2

I have spatialite geometry data stored in a spatialite database in WGS 84 (EPSG 4328) format:

SELECT Srid(geometry) FROM shape_layer;
> 4326

When geometry is being returned as text, it is returned in meters (when the data is being worked on, the projection is more commonly EPSG 3857 to make use of Google Maps/OpenStreetMap). By example:

SELECT AsText(geometry) FROM shape_layer;
> LINESTRING(-381578.202 6574042, -378511.828 6574794.956)

All data works will display correctly (e.g. if using QGIS), but this is giving me issues when using spatial queries. GLength returns a non-projected length (3.16km) rather than the actual (Ellipsoidal) length (2km). I'm assuming I'm going to have similar issues with area.

QGIS can do this simply by checking the Ellipsoid option for measuring length/area so I know its possible with the data I have. But how can I get the actual values for length and area from this data using spatialite?

I'm hoping this is a pretty simple thing to resolve. Thanks in advance for any help you can provide!

Improve this question

1 Answer 1

Reset to default
7

That should not happen (4236 uses degrees) . AsText() returns WKT geometry always in current srid, so you probably use wrong srid. You can set correct srid using UPDATE table SET geom = SetSrid(geom, 3857) assuming that your data really is in google mercerator. If you need to transform geometry then you can use UPDATE table SET geom = Transform(geom, 4326) This needs that original srid is correct, to work properly

In QGIS , you should set layer CRS correctly and there is also an option for measument setting in "Project" -> "project Properties" -> "General" (QGIS 2.1)

Improve this answer
4
  • Hi Simplexio, Thanks for the information. So to clarify: even though I define the layer as Srid 4326 when it is created and loaded into QGIS, features are being written with coordinates as if it is a Srid 3857 layer (as this is the working unit of the currently active map)? Sounds like a simple enough thing to correct anyway :-)
    – Dan
    Commented Jan 20, 2014 at 14:40
  • 3
    Defining a layer SRID does not reproject the coordinates to that CRS. If they were in metres before, they still are in metres.
    – AndreJ
    Commented Jan 20, 2014 at 15:27
  • So I have corrected the issue with CRS. But the value returned is still the planar distance (3.1km) rather than the actual (ellipsoidal) distance. QGIS can do this calculation using the ruler tool. I'd love to know what the calculation is!
    – Dan
    Commented Jan 21, 2014 at 10:32
  • "Project Properties" -> Measure tool -> ellipsoid , defines ellipsoid , under measurement tools is "Canvas units" where you can change units. but that is not your answer to your question ? Transform data to 4326 and get measured values in degrees ?
    – simpleuser001
    Commented Jan 21, 2014 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.