[gdal-dev] ogr2ogr project/transform CRS in the same database table
Rahkonen Jukka (MML)
jukka.rahkonen at maanmittauslaitos.fi
Wed Dec 2 02:55:03 PST 2020
Hi,
Sorry, I can’t follow what did you test. Do you mean that
ogrinfo -dialect SQLite -sql "select * from crown_site_data limit 1" "$connect_string"
returns just an error? And the same with
ogrinfo -dialect SQLite -sql "select * from \"crown_site_data\" limit 1" "$connect_string"
Try also with “-dialect ogrsql” even that dialect does not have those ST_ functions that you need.
-Jukka-
Lähettäjä: Hector muro <muro.hector at gmail.com>
Lähetetty: keskiviikko 2. joulukuuta 2020 12.37
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi>
Kopio: gdal-dev at lists.osgeo.org
Aihe: Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
Hi,
Yeah, I did try that, with the same result. the test with "sql" (no dialect) returns correct.
ogrinfo -sql "select top(1) * from <table_name>" "$connect_string" :
[dev at localhost ~]$ ogrinfo -sql "select top(1) * from crown_site_data" "$connect_string"
ERROR 1: Error initializing the metadata tables : [S0001][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'geometry_columns' in the database.(2714)
INFO: Open of `MSSQL:server=localhost;database=INMWS_GEOM_TESTS;UID=dev;PWD=DevDev1234;DRIVER=ODBC Driver 17 for SQL Server'
using driver `MSSQLSpatial' successful.
Layer name: SELECT
Geometry: Unknown (any)
Feature Count: 1
Layer SRS WKT:
PROJCRS["OSGB 1936 / British National Grid",
BASEGEOGCRS["OSGB 1936",
DATUM["OSGB 1936",
ELLIPSOID["Airy 1830",6377563.396,299.3249646,
LENGTHUNIT["metre",1]]],
PRIMEM["Greenwich",0,
ANGLEUNIT["degree",0.0174532925199433]],
ID["EPSG",4277]],
CONVERSION["British National Grid",
METHOD["Transverse Mercator",
ID["EPSG",9807]],
PARAMETER["Latitude of natural origin",49,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8801]],
PARAMETER["Longitude of natural origin",-2,
ANGLEUNIT["degree",0.0174532925199433],
ID["EPSG",8802]],
PARAMETER["Scale factor at natural origin",0.9996012717,
SCALEUNIT["unity",1],
ID["EPSG",8805]],
PARAMETER["False easting",400000,
LENGTHUNIT["metre",1],
ID["EPSG",8806]],
PARAMETER["False northing",-100000,
LENGTHUNIT["metre",1],
ID["EPSG",8807]]],
CS[Cartesian,2],
AXIS["(E)",east,
ORDER[1],
LENGTHUNIT["metre",1]],
AXIS["(N)",north,
ORDER[2],
LENGTHUNIT["metre",1]],
USAGE[
SCOPE["unknown"],
AREA["UK - Britain and UKCS 49°46'N to 61°01'N, 7°33'W to 3°33'E"],
BBOX[49.75,-9.2,61.14,2.88]],
ID["EPSG",27700]]
Data axis to CRS axis mapping: 1,2
Geometry Column = geometry
[...]
So, it is not the formatting, but rather what I supposed that sqlite has no visibility over the SQL Server schema, which surprises me a bit, since it can actually connect using the MSSQLSpatial Driver and retrieve info without it.
Thanks
Hector
On Wed, 2 Dec 2020 at 10:29, Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>> wrote:
Hi,
I am not familiar with SQL server but your table name may require quotation marks " " and they require escaping with \ so that they do not close the SQL statement. Try
-sql "update \"table_name\" set …. "
Simple test to verify if the problem is caused by the unquoted table name is to run ogrinfo with
-sql "select * from table_name limit 1".
-Jukka-
Lähettäjä: Hector muro <muro.hector at gmail.com<mailto:muro.hector at gmail.com>>
Lähetetty: keskiviikko 2. joulukuuta 2020 12.13
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Kopio: gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
Aihe: Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
Hi again,
I've given your idea a go:
ogrinfo -dialect sqlite -sql "update <table_name> set <text_geom> = ST_AsText(ST_Transform(geometry),4326)" --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO "$connect_string"
This connects correctly, as I get this message:
INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xx;DRIVER=ODBC Driver 17 for SQL Server'
using driver `MSSQLSpatial' successful.
But it can't read/find the table in the update statement:
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(update <table_name> set <text_geom> = ST_AsText(ST_Transform(geometry),4326)):
no such table: <table_name>
(I have tried different versions of the table name, quoting, unquoting, adding the Schema name . table name, with the same results.) I now wonder if the sqlite dialect can't really read what's inside my SQL Server instance? Is it maybe too much what I am trying to do? (too much for SQL Server's limitations).
Thanks again,
Hector
On Tue, 1 Dec 2020 at 20:56, jratike80 <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>> wrote:
I would say that it is best to still use ogrinfo. You did not tell what is
your database so I can't tell the exact recipe for you but you will learn
it. You need to know that there are different SQL dialects for GDAL: OGR
SQL, SQLite, indirect_SQLite, and the native SQL for your database. They
are all documented. If your database does not have native ST_Transform
support then you take it from SQLite/Spatialite. For overriding the native
SQL of your database you may need to use -dialect indirect_SQLite.
Here is another example with a shapefile. A combination of OGR SQL and
SQLite dialects is needed.
Add a new column
ogrinfo -sql "alter table states add wkt_geom text" states.shp
There is space for only 254 charactes in a shapefile and that is not enough
for big polygons but we can do at least something with a little trick.
ogrinfo -dialect sqlite -sql "update states set
wkt_geom=ST_AsText(ST_Transform(ST_Centroid(geometry),3857))" states.shp
Check what we got
ogrinfo states.shp -al
...
SERVICE (Real) = 637487.000000000
MANUAL (Real) = 302635.000000000
P_MALE (Real) = 0.496000000
P_FEMALE (Real) = 0.504000000
SAMP_POP (Real) = 736744.000000000
wkt_geom (String) = POINT(-13405860.170799 6003812.424048)
MULTIPOLYGON (((-122.400749 48.225395,-122.461586 48.228542,-122.453156
48.128674,-122.360077 48.06028,-122.513245 48.134155,-122.540802
48.21064,-122.507858 48.253971,-122.403137 48....
-Jukka Rahkonen-
hectormauer wrote
> Oh, I see. That's great.
>
> But what if the Database does not have ST_Transform support? Could we
> still
> achieve a similar approach by means of ogr2ogr?
>
> (I should have mentioned this in my first email).
>
> Thanks again!
> Hector
>
> On Tue, 1 Dec 2020 at 20:14, jratike80 <
> jukka.rahkonen@<mailto:jukka.rahkonen@>
> >
> wrote:
>
>> Hi,
>>
>> No, unfortunately it is not possible with one ogr2ogr command.
>>
>> You need two commands and you must use ogrinfo. Here an example about
>> adding
>> another geometry column and populating it with EPSG:3857 version of
>> original
>> EPSG:4326 geometries.
>>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user
>> password=password" -sql "alter table states add column geom2 geometry"
>>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user
>> password=password" -sql "update states set
>> geom2=ST_Transform(wkb_geometry,3857)"
>>
>> You can do all that is possible with SQL also with ogrinfo.
>>
>>
>> -Jukka Rahkonen-
>>
>>
>>
>> hectormauer wrote
>> > Hi all,
>> >
>> > I was wondering if someone knew if it's possible to project or
>> transform
>> > within the same table in a database, using ogr2ogr.
>> >
>> > What I mean is, I have a table with fields: id, id2, geom_wkt_27700 and
>> I
>> > would like, using a single command to create an extra column
>> > "geom_wkt_4326" for instance. It does not have to be of type geometry,
>> but
>> > could be text for instance.
>> >
>> > I guess the other plausible solution would be to create a temporary
>> table
>> > with that transformation on it and reference it to the first one by id
>> for
>> > instance.
>> >
>> > Any help is appreciated!
>> >
>> > Thanks,
>> > Hector
>> >
>> > _______________________________________________
>> > gdal-dev mailing list
>>
>> > gdal-dev at .osgeo<mailto:gdal-dev at .osgeo>
>>
>> > https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>>
>>
>>
>>
>> --
>> Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
>> _______________________________________________
>> gdal-dev mailing list
>>
> gdal-dev at .osgeo<mailto:gdal-dev at .osgeo>
>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at .osgeo<mailto:gdal-dev at .osgeo>
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org<mailto:gdal-dev at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/gdal-dev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20201202/00291f7e/attachment-0001.html>
More information about the gdal-dev
mailing list