0

I'm somewhat of a GIS novice and am trying to see if this is possible to save me some time with further investigating. I have about 300 records in a CSV file with the fields SPC Zone, ECOORD, NCOORD, CRDUNITS, and a point identifier.

I was wondering if there is any possible way to batch convert these points (all with differing SPC Zones) to lat long in QGIS or some other application?

0

Here's a partial solution, and I think there might be a bug, but here goes.

Given a CSV that looks like this:

enter image description here

You can load the CSV into QGIS and use the DB Manager Query window to perform SQLite / sql spatial functions on your data.

select 
c.*
, make_point(c.x, c.y) as geom 
from libraries_denver_csv as c

This basically turns the two coordinate values into a geometry object that can be drawn on the map:

enter image description here

Now what I thought would be possible would be to simply apply the ST_Transform / Transform function on that constructed geometry and use the value in the spc_zone column to serve as the SRID integer for that transformation.

However, I keep getting a NULL response, and can't figure out why - perhaps this doesn't exactly work in SQLite - and if anyone can add some info, I would appreciate it (as this question came up this morning on a local GIS forum as well):

select 
c.*
, make_point(c.x, c.y) as geom 
--test with value from spc_zone column
, Transform((make_point(c.x, c.y)), cast(spc_zone as integer)) as geom_transform
--test with single known SRID value to apply for all records
, Transform((make_point(c.x, c.y)), 3857) as geom_transform

from libraries_denver_csv as c

But unfortunately this consistently yields a NULL response:

enter image description here

Does anyone know why that might be happening?

4
  • The x and y columns are longitude and latitude. spc_zone has what is probably EPSG:3857, AKA 'web mercator' or 'WGS 84 / Pseudo-Mercator'. Try using 4269 (NAD83) or 4326 (WGS84) instead.
    – mkennedy
    Aug 7 '20 at 19:17
  • @mkennedy Hi Melita - I think this might be a bug with the DB Manager transform functions - this works on an existing geometry (from a SHP, for example), but not when trying the Transform(geom, srid) function on the geometry built from makepoint(x,y) in DB Manager.... Aug 7 '20 at 20:26
  • Ah, I see. Can you figure out what coordinates are in the geom?
    – mkennedy
    Aug 7 '20 at 21:07
  • @mkennedy in my example above I'm using WGS84/4326. The 'geom' column is the result of the makepoint(x,y) function. Aug 7 '20 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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