2

Someone is sending me a CSV file with a list of points that I am trying to map. But instead of the usual Lat and Long there is just a Geometry field (see examples below). I have tried several methods to open this, assuming 3857 is the EPSG - not sure what the 2001 is for. How can I plot these coordinates on a map in QGIS? I have asked for a new file with the usual Lat and Long but would like to try and figure this out while I wait for it if possible. 

(2001, 3857, (-8863473.81677258, 5417238.01101921, ), , )
(2001, 3857, (-9247508.24889085, 5207353.52931172, ), , )
(2001, 3857, (-9243598.70837419, 5208396.74498673, ), , )

enter image description here

Improve this question
0

1 Answer 1

Reset to default
4

This seems to be a slightly modified version of Oracle's SDO_GEOMETRY (see https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL494 for documentation).

The 2001 means every entry is a 2 dimensional point. The 3857 indeed means the SRS is EPSG:3857. The 2 varying numbers should therefore be the x and y coordinates in this SRS.

What is unusual is that when I have had the (mis)fortune of coming across geometry specified this way rather than today's more common WKT, etc., it has been labeled SDO_GEOMETRY rather than GEOMETRY as in your file.

I doubt it will work, but worth a try to just replace GEOMETRY with SDO_GEOMETRY and see if the CSV reading functionality of QGIS might then be able to handle it.

Failing that, I'd import it as a point layer in EPSG:3857, and then use field calculator to update the geometry field using geom_from_wkt and regexp_substr or a similar ad-hoc parsing algorighm.

Improve this answer
3
  • yes it is exported from an Oracle DB. The SDO suggestion didn't work, I will try the field calculator. thank you for your reply!!
    – WSeanMac
    Commented Apr 26, 2020 at 23:39
  • 1
    If all the points are exactly the same format and with same number of digits, then geom_from_wkt(replace('POINT('||left(right("SDO_Geom_String",43),35)||')',',',''))) should do it if you don't feel like mucking around with regular expressions. Lops off the junk at left and right, kills the comma between x and y, and recasts it as WKT and then a geometry.
    – Houska
    Commented Apr 27, 2020 at 0:45
  • 1
    thank you! ended up just fixing the original query in Oracle to include {SDO_CS.TRANSFORM(slb.geometry, 4326).SDO_POINT.y AS latitude, SDO_CS.TRANSFORM(slb.geometry, 4326).SDO_POINT.x AS longitude}
    – WSeanMac
    Commented May 5, 2020 at 17:13

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.