Hi,
Your dataset with overlapping polygons feels like a perfect candidate for
trying these overlay tricks
http://blog.cleverelephant.ca/2019/07/postgis-overlays.html. If you would like
to try a desktop program OpenJUMP can do the job with two tools: Edit
geometry-Convert-Planar graph and Analy
Hi Jukka,
Thanks for all the dialect info.
Reading your last message about testing the dialect in use, I have to admit
that I did not think about native sql
(dialect) being deduced from the source type rather than from any dbms
involved.
That is clear to me now, of course.
Would it be correct, t
Hi
Thanks again - that explains my confusion. So there is really three SQL dialect
options with quite variable capabilities and issues.
Or four if the INDIRECT_SQLITE is an own dialect as mentioned in
https://gdal.org/programs/ogrinfo.html -dialect
I assume that ”nativeSQL” is the default diale
Hi,
Sorry, I did not read well enough about what you were doing. You wrote
"slightly more involved sql on a postgresql table requires dialect=sqlite to
work at all" but in your example you are reading data from a shapefile
"alltogether_pruned_out.shp". That you write data into PostGIS does not
Hi,
You are getting closer but at the bottom of the page
https://gdal.org/user/ogr_sql_dialect.html there is an important paragraph:
Non-OGR SQL
All OGR drivers for database systems: MySQL, PostgreSQL / PostGIS, Oracle
Spatial, SQLite / Spatialite RDBMS, ODBC RDBMS, ESRI Personal GeoDatabase, S
For the SQLite dialect, the page to consult is
https://gdal.org/user/sql_sqlite_dialect.html
Le 09/06/2022 à 11:47, Andreas Oxenstierna a écrit :
Hi
Thanks a lot and sorry for the noise.
UPDATE do work as expected using the OGR SQL, i.e. one atomic db
transaction executing in the database = v
Hi Jukka,
Not entirely sure what you mean by 'have you tested the sql with psql',
since this is an ogr2ogr process which works.
It is a remnant of a case where multiple iv (iv1, iv2 etc.) shapefile
columns were needed, so the sql used to be
like
coalesce(iv1,'')||coalesce(iv2,'')||coalesce(iv2,'')|
Hi
Thanks a lot and sorry for the noise.
UPDATE do work as expected using the OGR SQL, i.e. one atomic db transaction
executing in the database = vastly faster than using -dialect sqlite
.
The doc at https://gdal.org/user/ogr_sql_dialect.html needs some addition - I
assume that INSERT, DELETE et
Hi,
This updated 166000 rows in 15 seconds for me on my laptop without any
workarounds:
ogrinfo PG:"host=localhost port=5432 dbname=my_pg user=user password=pw" -sql
"update buildingtest set version=99"
-Jukka Rahkonen-
Lähettäjä: Andreas Oxenstierna
Lähetetty: torstai 9. kesäkuuta 2022 12.02
Hi
AFAIK, UPDATE only works with ogrinfo and the sqlite dialect. We have not
tested the native PG SQL dialect extensively though, it took some time to find
the correct syntax.
And to answer one other mail, the table name needs to be written as
\”.\”, at least on macOS
The performance issue is
Hi Jan,
I had a try with a table that I created from the "states" shapefile from the
Geoserver demo data.
ogrinfo PG:"host=localhost port=5432 dbname=my_pg user=user password=pw" -sql
"select trim(coalesce(state_name,state_fips),';') as fidstring, state_fips,
st_union(wkb_geometry) as multipol
Afaik as I know, slightly more involved sql on a postgresql table requires
dialect=sqlite to work at all, e.g. (picked a random example using ogr2ogr
instead of ogrinfo)
ogr2ogr -f postgresql -dialect sqlite -append PG:"user=%user%
dbname=%dbname%" -sql "select trim(coalesce(iv1,''),';') as fidstri
Hi again,
It might be good to know how the SQLite dialect works. When it is used for
other datasources than natively SQLite based ones then GDAL creates a virtual
table into a SQLite database with a VirtualOGR system. There is some
information about that in
https://www.gaia-gis.it/fossil/libsp
Hi,
Do not use "-dialect sqlite" if you play with PostgreSQL but let GDAL to use
the native PG SQL dialect.
-Jukka Rahkonen-
Lähettäjä: gdal-dev Puolesta Andreas
Oxenstierna
Lähetetty: torstai 9. kesäkuuta 2022 9.50
Vastaanottaja: gdal-dev@lists.osgeo.org
Aihe: [gdal-dev] ogrinfo UPDATE perfo
Of course I normally do this directly in the database with pgAdmin or psql.
In this process scenario it is preferable if the complete process (this is the
final step of approx. 10 gdal/ogr processes) can be executed in batch by a
client with no psql access.
We have to use psql if there is no acc
15 matches
Mail list logo
