[gdal-dev] Using field name as variable in a "dialect sqlite" query

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] Using field name as variable in a "dialect sqlite" query

aborruso
Hi,
I have this kind of CSV

ID,x,y,epsg
1,500000,4000000,32633
1,500000,4000000,32632

Is it possible to use ogrinfo and a "dialect sqlite" query in this way, and
set the EPSG by reading the value from the `epsg` field?

ogrinfo -dialect sqlite -sql "select ST_GeomFromText('POINT(' || x || ' ' ||
y || ')',epsg) from source" source.csv source

If yes, how?

Thank you



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Using field name as variable in a "dialect sqlite" query

jratike80
aborruso wrote

> Hi,
> I have this kind of CSV
>
> ID,x,y,epsg
> 1,500000,4000000,32633
> 1,500000,4000000,32632
>
> Is it possible to use ogrinfo and a "dialect sqlite" query in this way,
> and
> set the EPSG by reading the value from the `epsg` field?
>
> ogrinfo -dialect sqlite -sql "select ST_GeomFromText('POINT(' || x || ' '
> ||
> y || ')',epsg) from source" source.csv source
>
> If yes, how?
>
> Thank you

You can have only one SRID on a layer so you must either split your data by
the epsg field or transform each record to same SRID. Otherwise your request
was almost complete.

I wonder if anybody has tried to make SQL injection through the name of the
geometry column :)

ogrinfo -dialect sqlite -sql "select ST_GeomFromText('POINT('|| x || ' ' ||
y || ')',CAST(epsg AS integer)) from coords" coords.csv
INFO: Open of `coords.csv'
      using driver `CSV' successful.

Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 2
Extent: (500000.000000, 4000000.000000) - (500000.000000, 4000000.000000)
Layer SRS WKT:
PROJCS["WGS 84 / UTM zone 33N",
    GEOGCS["WGS 84",
        DATUM["WGS_1984",
            SPHEROID["WGS 84",6378137,298.257223563,
                AUTHORITY["EPSG","7030"]],
            AUTHORITY["EPSG","6326"]],
        PRIMEM["Greenwich",0,
            AUTHORITY["EPSG","8901"]],
        UNIT["degree",0.0174532925199433,
            AUTHORITY["EPSG","9122"]],
        AXIS["Latitude",NORTH],
        AXIS["Longitude",EAST],
        AUTHORITY["EPSG","4326"]],
    PROJECTION["Transverse_Mercator"],
    PARAMETER["latitude_of_origin",0],
    PARAMETER["central_meridian",15],
    PARAMETER["scale_factor",0.9996],
    PARAMETER["false_easting",500000],
    PARAMETER["false_northing",0],
    UNIT["metre",1,
        AUTHORITY["EPSG","9001"]],
    AXIS["Easting",EAST],
    AXIS["Northing",NORTH],
    AUTHORITY["EPSG","32633"]]
Geometry Column = ST_GeomFromText('POINT('|| x || ' ' || y || ')',CAST(epsg
AS integer))
OGRFeature(SELECT):0
  POINT (500000 4000000)

OGRFeature(SELECT):1
  POINT (500000 4000000)

-Jukka Rahkonen-




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Using field name as variable in a "dialect sqlite" query

aborruso
Hi Jukka,


jratike80 wrote
> You can have only one SRID on a layer so you must either split your data
> by
> the epsg field or transform each record to same SRID. Otherwise your
> request
> was almost complete.

I know, but my problem was the source!


jratike80 wrote
> ogrinfo -dialect sqlite -sql "select ST_GeomFromText('POINT('|| x || ' '
> ||
> y || ')',CAST(epsg AS integer)) from coords" coords.csv

And thank you to your example I have found the solution that was right for
me:

ogrinfo -dialect sqlite -sql "select ST_TRANSFORM(ST_GeomFromText('POINT('||
x || ' ' || y || ')',CAST(epsg AS integer)),4326) from coords" coords.csv

It gives me

OGRFeature(SELECT):0
  POINT (15.0 36.1447180988163)

OGRFeature(SELECT):1
  POINT (9.0 36.1447180988163)

Than I have unified all, without splitting my source file (as you have
written I have trasformed each record to same SRID).

You and ogr are great :)




--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev