[gdal-dev] Create PostGIS database using OGR/GDAL

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] Create PostGIS database using OGR/GDAL

Paul Meems
Hi,

At this moment we are working on getting PostGIS support for MapWinGIS.
We use the PostGIS driver from GDALv2.

So far we can read and write layers.
We also want to implement some tests, which are also used as a show case for other users.

For these tests I want to create a database, import some layers, doe something with the layers and drop the database again.

Creating and dropping a database using ExecuteSQL() doesn't work as stated at the driver page:
The PostgreSQL driver does not support creation of new datasets (a database within PostgreSQL), but it does allow creation of new layers within an existing database.
The error I'm getting is "CREATE DATABASE cannot run inside a transaction block".

Does anybody have an idea how to solve this? I don't want to manually create the database. The tests are more or less running automated. I would also like to prevent to bypass GDAL/OGR and connect to PostGIS directly to create and drop a database.

Any suggestions are much appreciated.

Thanks,

Paul

Paul Meems 
Release manager, configuration manager
and forum moderator of MapWindow GIS.
www.mapwindow.org

Owner of MapWindow.nl - Support for
Dutch speaking users.
www.mapwindow.nl


The English presentations of the MapWindow GIS Conference 2014 are online.


_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Create PostGIS database using OGR/GDAL

M. Edward (Ed) Borasky
If this is a Windows-only project, you can write directly to PostgreSQL / PostGIS using the ODBC driver. And if you have shapefiles, there's a PostGIS utility called "shp2pgsql" that will import them.

On Thu, Oct 23, 2014 at 1:03 PM, Paul Meems <[hidden email]> wrote:
Hi,

At this moment we are working on getting PostGIS support for MapWinGIS.
We use the PostGIS driver from GDALv2.

So far we can read and write layers.
We also want to implement some tests, which are also used as a show case for other users.

For these tests I want to create a database, import some layers, doe something with the layers and drop the database again.

Creating and dropping a database using ExecuteSQL() doesn't work as stated at the driver page:
The PostgreSQL driver does not support creation of new datasets (a database within PostgreSQL), but it does allow creation of new layers within an existing database.
The error I'm getting is "CREATE DATABASE cannot run inside a transaction block".

Does anybody have an idea how to solve this? I don't want to manually create the database. The tests are more or less running automated. I would also like to prevent to bypass GDAL/OGR and connect to PostGIS directly to create and drop a database.

Any suggestions are much appreciated.

Thanks,

Paul

Paul Meems 
Release manager, configuration manager
and forum moderator of MapWindow GIS.
www.mapwindow.org

Owner of MapWindow.nl - Support for
Dutch speaking users.
www.mapwindow.nl


The English presentations of the MapWindow GIS Conference 2014 are online.


_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev



--
Twitter: http://twitter.com/znmeb; Computational Journalism on a Stick http://j.mp/CompJournoStickOverview

Remember, if you're traveling to Bactria, Hump Day is Tuesday and Thursday.

_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Create PostGIS database using OGR/GDAL

Paul Meems
Thanks for your reply.

MapWinGIS is indeed a Windows-only project written in C++.
My test application is written in C#, so I can use npsql to connect to PostGIS and send the 'Create database' statement.

But I'm mostly wondering why I can do everything with the PostGIS driver, except create or drop a database.
Perhaps the driver needs a ExecuteNonQuery() function, like npsql does?




Paul

Paul Meems 
Release manager, configuration manager
and forum moderator of MapWindow GIS.
www.mapwindow.org

Owner of MapWindow.nl - Support for
Dutch speaking users.
www.mapwindow.nl


The English presentations of the MapWindow GIS Conference 2014 are online.


2014-10-23 22:48 GMT+02:00 M. Edward (Ed) Borasky <[hidden email]>:
If this is a Windows-only project, you can write directly to PostgreSQL / PostGIS using the ODBC driver. And if you have shapefiles, there's a PostGIS utility called "shp2pgsql" that will import them.

On Thu, Oct 23, 2014 at 1:03 PM, Paul Meems <[hidden email]> wrote:
Hi,

At this moment we are working on getting PostGIS support for MapWinGIS.
We use the PostGIS driver from GDALv2.

So far we can read and write layers.
We also want to implement some tests, which are also used as a show case for other users.

For these tests I want to create a database, import some layers, doe something with the layers and drop the database again.

Creating and dropping a database using ExecuteSQL() doesn't work as stated at the driver page:
The PostgreSQL driver does not support creation of new datasets (a database within PostgreSQL), but it does allow creation of new layers within an existing database.
The error I'm getting is "CREATE DATABASE cannot run inside a transaction block".

Does anybody have an idea how to solve this? I don't want to manually create the database. The tests are more or less running automated. I would also like to prevent to bypass GDAL/OGR and connect to PostGIS directly to create and drop a database.

Any suggestions are much appreciated.

Thanks,

Paul

Paul Meems 
Release manager, configuration manager
and forum moderator of MapWindow GIS.
www.mapwindow.org

Owner of MapWindow.nl - Support for
Dutch speaking users.
www.mapwindow.nl


The English presentations of the MapWindow GIS Conference 2014 are online.


_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev



--
Twitter: http://twitter.com/znmeb; Computational Journalism on a Stick http://j.mp/CompJournoStickOverview

Remember, if you're traveling to Bactria, Hump Day is Tuesday and Thursday.


_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: Create PostGIS database using OGR/GDAL

Even Rouault-2
Le vendredi 24 octobre 2014 09:45:03, Paul Meems a écrit :
> Thanks for your reply.
>
> MapWinGIS is indeed a Windows-only project written in C++.
> My test application is written in C#, so I can use npsql to connect to
> PostGIS and send the 'Create database' statement.
>
> But I'm mostly wondering why I can do everything with the PostGIS driver,
> except create or drop a database.
> Perhaps the driver needs a ExecuteNonQuery() function, like npsql does?

Paul,

Up to know ExecuteSQL() ran passed SQL inside a transaction except when it was
a VACUUM that requires NOT to be run inside any transaction. Apparently CREATE
DATABASE too. I've reverted the logic (in trunk): now  passed SQL is always
run outside of transaction except in the case of SELECT ... FROM .... where we
actually create a cursor to scroll the results, which require a transaction.
So now I can run CREATE DATABASE

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/gdal-dev