[Gdal-dev] Problems using ogr -sql "..." with postgis 1.0
Asger Petersen
asger at jo-informatik.dk
Wed Apr 6 07:05:27 EDT 2005
Hi
I'm having some trouble using the -sql option with postgis 1.0rc4.
As far as I can see, the -sql option works with postgis 0.91 but it
doesn't work with postgis 1.0rc4. I have looked through gdal- and
postgismailinglists without seeing anyone mentioning this.
When trying to dump from postgis to shapefiles I get shapefiles with all
nullgeometries. See below.
Any ideas?
Regards
Asger
Here is a simple test to demonstrate the problem:
---------------------------------------------------------------
-- postgis 1.0 rc4
-- doesn't use -sql
---------------------------------------------------------------
C:\tmp\gdal_test>set path=c:\buildenv\gdalcvs\bld\bin\
C:\tmp\gdal_test>set GDAL_DATA=c:\buildenv\gdalcvs\bld\data\
C:\tmp\gdal_test>ogrinfo -so test.shp test
INFO: Open of `test.shp'
using driver `ESRI Shapefile' successful.
Layer name: test
Geometry: Line String
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_1984",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]]
FRC: Integer (2.0)
NAME: String (70.0)
NAMELC: String (3.0)
ROUTENUM: String (10.0)
RTETYP: Integer (2.0)
RTEDIR: String (2.0)
RTEDIRVD: String (2.0)
C:\tmp\gdal_test>ogr2ogr -f PostgreSQL PG:"dbname=X hostaddr=X.X.X.205
user=X pa
ssword=X" -lco overwrite=yes -lco dim=2 -a_srs EPSG:25832 -nln test
test.shp
C:\tmp\gdal_test>ogrinfo -so PG:"dbname=teleatlas hostaddr=X.X.X.205
user=X password=X" test
INFO: Open of `PG:dbname=teleatlas hostaddr=X.X.X.205 user=X password=X'
using driver `PostgreSQL' successful.
Layer name: test
Geometry: Unknown (any)
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
PROJCS["ETRS89 / UTM zone 32N",
GEOGCS["ETRS89",
DATUM["European_Terrestrial_Reference_System_1989",
SPHEROID["GRS 1980",6378137,298.257222101,
AUTHORITY["EPSG","7019"]],
AUTHORITY["EPSG","6258"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4258"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",9],
PARAMETER["scale_factor",0.9996],
PARAMETER["false_easting",500000],
PARAMETER["false_northing",0],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
AUTHORITY["EPSG","25832"]]
frc: Integer (2.0)
name: String (70.0)
namelc: String (3.0)
routenum: String (10.0)
rtetyp: Integer (2.0)
rtedir: String (2.0)
rtedirvd: String (2.0)
C:\tmp\gdal_test>ogr2ogr -f "ESRI Shapefile" -lco SHPT=ARC testdmp.shp
PG:"dbname=teleatlas hostaddr
=X.X.X.205 user=X password=X" test
C:\tmp\gdal_test>ogrinfo -so testdmp.shp testdmp
INFO: Open of `testdmp.shp'
using driver `ESRI Shapefile' successful.
Layer name: testdmp
Geometry: Line String
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
PROJCS["ETRS89 / UTM zone 32N",
GEOGCS["ETRS89",
DATUM["European_Terrestrial_Reference_System_1989",
SPHEROID["GRS 1980",6378137,298.257222101,
AUTHORITY["EPSG","7019"]],
AUTHORITY["EPSG","6258"]],
PRIMEM["Greenwich",0,
AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,
AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4258"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",9],
PARAMETER["scale_factor",0.9996],
PARAMETER["false_easting",500000],
PARAMETER["false_northing",0],
UNIT["metre",1,
AUTHORITY["EPSG","9001"]],
AUTHORITY["EPSG","25832"]]
frc: Integer (2.0)
name: String (70.0)
namelc: String (3.0)
routenum: String (10.0)
rtetyp: Integer (2.0)
rtedir: String (2.0)
rtedirvd: String (2.0)
------------------------------------------------------------------------
---
-- postgis 1.0 rc4
-- uses -sql
------------------------------------------------------------------------
---
C:\tmp\gdal_test>set path=c:\buildenv\gdalcvs\bld\bin\
C:\tmp\gdal_test>set GDAL_DATA=c:\buildenv\gdalcvs\bld\data\
C:\tmp\gdal_test>ogrinfo -so test.shp test
INFO: Open of `test.shp'
using driver `ESRI Shapefile' successful.
Layer name: test
Geometry: Line String
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_1984",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]]
FRC: Integer (2.0)
NAME: String (70.0)
NAMELC: String (3.0)
ROUTENUM: String (10.0)
RTETYP: Integer (2.0)
RTEDIR: String (2.0)
RTEDIRVD: String (2.0)
C:\tmp\gdal_test>ogr2ogr -f PostgreSQL PG:"dbname=teleatlas
hostaddr=X.X.X.205 user=X pa
ssword=X" -lco overwrite=yes -lco dim=2 -a_srs EPSG:25832 -nln test
test.shp
C:\tmp\gdal_test>ogrinfo -so PG:"dbname=teleatlas hostaddr=X.X.X.205
user=X password=X" -sql "select * from test"
INFO: Open of `PG:dbname=teleatlas hostaddr=X.X.X.205 user=X password=X'
using driver `PostgreSQL' successful.
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 5
Layer SRS WKT:
(unknown)
frc: String (0.0)
name: String (0.0)
namelc: String (0.0)
routenum: String (0.0)
rtetyp: String (0.0)
rtedir: String (0.0)
rtedirvd: String (0.0)
C:\tmp\gdal_test>ogr2ogr -f "ESRI Shapefile" -lco SHPT=ARC testdmp.shp
PG:"dbname=teleatlas hostaddr
=X.X.X.205 user=X password=X" -sql "select * from test"
C:\tmp\gdal_test>ogrinfo -so testdmp.shp testdmp
INFO: Open of `testdmp.shp'
using driver `ESRI Shapefile' successful.
Layer name: testdmp
Geometry: Line String
Feature Count: 5
Extent: (0.000000, 0.000000) - (0.000000, 0.000000)
Layer SRS WKT:
(unknown)
frc: String (80.0)
name: String (80.0)
namelc: String (80.0)
routenum: String (80.0)
rtetyp: String (80.0)
rtedir: String (80.0)
rtedirvd: String (80.0)
------------------------------------------------------------------------
---
-- postgis 0.91
-- uses -sql
------------------------------------------------------------------------
---
C:\tmp\gdal_test>set path=c:\buildenv\gdalcvs\bld\bin\
C:\tmp\gdal_test>set GDAL_DATA=c:\buildenv\gdalcvs\bld\data\
C:\tmp\gdal_test>ogrinfo -so test.shp test
INFO: Open of `test.shp'
using driver `ESRI Shapefile' successful.
Layer name: test
Geometry: Line String
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
GEOGCS["GCS_WGS_1984",
DATUM["WGS_1984",
SPHEROID["WGS_1984",6378137,298.257223563]],
PRIMEM["Greenwich",0],
UNIT["Degree",0.017453292519943295]]
FRC: Integer (2.0)
NAME: String (70.0)
NAMELC: String (3.0)
ROUTENUM: String (10.0)
RTETYP: Integer (2.0)
RTEDIR: String (2.0)
RTEDIRVD: String (2.0)
C:\tmp\gdal_test>ogr2ogr -f PostgreSQL PG:"dbname=teleatlas
hostaddr=X.X.X.203 user=X pa
ssword=X" -lco overwrite=yes -lco dim=2 -a_srs EPSG:25832 -nln test
test.shp
C:\tmp\gdal_test>ogrinfo -so PG:"dbname=teleatlas hostaddr=X.X.X.203
user=X password=X" -sql "select * from test"
INFO: Open of `PG:dbname=teleatlas hostaddr=X.X.X.203 user=X password=X'
using driver `PostgreSQL' successful.
Layer name: sql_statement
Geometry: Unknown (any)
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
(unknown)
frc: String (0.0)
name: String (0.0)
namelc: String (0.0)
routenum: String (0.0)
rtetyp: String (0.0)
rtedir: String (0.0)
rtedirvd: String (0.0)
C:\tmp\gdal_test>ogr2ogr -f "ESRI Shapefile" -lco SHPT=ARC testdmp.shp
PG:"dbname=teleatlas hostaddr
=X.X.X.203 user=X password=X" -sql "select * from test"
C:\tmp\gdal_test>ogrinfo -so testdmp.shp testdmp
INFO: Open of `testdmp.shp'
using driver `ESRI Shapefile' successful.
Layer name: testdmp
Geometry: Line String
Feature Count: 5
Extent: (12.324736, 55.804259) - (12.523302, 56.000850)
Layer SRS WKT:
(unknown)
frc: String (80.0)
name: String (80.0)
namelc: String (80.0)
routenum: String (80.0)
rtetyp: String (80.0)
rtedir: String (80.0)
rtedirvd: String (80.0)
------------------------------------------------------------------------
---
More information about the Gdal-dev
mailing list