Quantcast

PostGIS layer import

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

PostGIS layer import

Moritz Lennert
Hi,

I know there have been discussions on this before, but I would like to
come back to the question of how to handle imports of layers from
PostGIS databases. Possibly there are some elements I still don't really
understand, so I'm launching this debate here, instead of as a bug report.

In short: I would like to plead for v.in.ogr to support exactly the same
syntax as ogr.

Example:

ogrinfo "PG:host=myhost user=theuser password=thepassword
dbname=thedatabase" theschema.thetable -so

gives me the correct information about a table and

ogr2ogr myshapefile.shp "PG:host=myhost user=theuser
password=thepassword dbname=thedatabase" theschema.thetable

gives me a shapefile corresponding to the content of the table.

I would, therefore, expect to just be able to do this:

v.in.ogr "PG:host=myhost user=theuser password=thepassword
dbname=thedatabase" layer=theschema.thetable out=mygrassmap

In GRASS 7.0 this works. According to the trunk man page, this should
also work (unless the example given (v.in.ogr input="PG:host=localhost
dbname=postgis user=postgres" layer=polymap output=polygons
type=boundary,centroid) is only valid for localhost connections.

However, I get the fatal error

"the database 'thedatabase' does not exist".

In order to get my layer, I have to jump through a series of hoops:

db.connect driver=pg database=thedatabase schema=theschema
db.login driver=pg database=thedatabase host=myhost user=theuser
password=thepassword
v.in.ogr input="PG:dbname=thedatabase" layer=theschema.thetable
out=mygrassmap

Only providing layer=thetable does not seem to work, even though the
schema was already defined using db.connect.

Besides being overly complicated (as I said, maybe I missed a simpler
way), this actually raises two very serious problems:

- As I changed the db.connect settings to read a database, all future
writing of attributes will now happen using these settings, which is not
at all what I want.
- If theuser only has 'select', but not 'create table' rights in the
database and schema, then the v.in.ogr call fails because it cannot
write the table (since it tries to write it to the PostGIS database
because of the db.connect settings).

I understand that there are many different situations in databases and
that it is difficult to handle them all, but (again: unless I'm
completely misunderstanding things) the current handling just seems not
only overly complicated, but actually with serious consequences and errors.

And I think we should not move away from ogr in v.in.ogr.

In addition, in the GUI import wizard, after having set db.login and
being able to see the available tables with 'v.in.ogr -l', the GUI
cannot list these same tables because it seems to attempt to connect to
the database 'template1' to which the defined user does not have access
rights.

Finally, db.login does not allow erasing existing settings, but the GUI
takes its information from the login file when determining where to
connect when "database" is chosen in the import wizard. One has to
manually erase the file (or relevant lines in the file).

Maybe a v.in.postgis handling special access needs might be a solution,
leaving v.in.ogr as a real frontend to ogr with exact same behaviour as
ogr ?

Please let me know if I just completely misunderstand the functioning of
v.in.ogr for PostGIS layers at this stage...

Moritz
_______________________________________________
grass-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS layer import

Markus Metz-3


On Wed, May 17, 2017 at 9:31 AM, Moritz Lennert <[hidden email]> wrote:

>
> Hi,
>
> I know there have been discussions on this before, but I would like to come back to the question of how to handle imports of layers from PostGIS databases. Possibly there are some elements I still don't really understand, so I'm launching this debate here, instead of as a bug report.
>
> In short: I would like to plead for v.in.ogr to support exactly the same syntax as ogr.
>
> Example:
>
> ogrinfo "PG:host=myhost user=theuser password=thepassword dbname=thedatabase" theschema.thetable -so
>
> gives me the correct information about a table and
>
> ogr2ogr myshapefile.shp "PG:host=myhost user=theuser password=thepassword dbname=thedatabase" theschema.thetable
>
> gives me a shapefile corresponding to the content of the table.
>
> I would, therefore, expect to just be able to do this:
>
> v.in.ogr "PG:host=myhost user=theuser password=thepassword dbname=thedatabase" layer=theschema.thetable out=mygrassmap

I agree
>

> In GRASS 7.0 this works. According to the trunk man page, this should also work (unless the example given (v.in.ogr input="PG:host=localhost dbname=postgis user=postgres" layer=polymap output=polygons type=boundary,centroid) is only valid for localhost connections.
>
> However, I get the fatal error
>
> "the database 'thedatabase' does not exist".
>
> In order to get my layer, I have to jump through a series of hoops:
>
> db.connect driver=pg database=thedatabase schema=theschema
> db.login driver=pg database=thedatabase host=myhost user=theuser password=thepassword
> v.in.ogr input="PG:dbname=thedatabase" layer=theschema.thetable out=mygrassmap
>
> Only providing layer=thetable does not seem to work, even though the schema was already defined using db.connect.
>
> Besides being overly complicated (as I said, maybe I missed a simpler way), this actually raises two very serious problems:
>
> - As I changed the db.connect settings to read a database, all future writing of attributes will now happen using these settings, which is not at all what I want.
> - If theuser only has 'select', but not 'create table' rights in the database and schema, then the v.in.ogr call fails because it cannot write the table (since it tries to write it to the PostGIS database because of the db.connect settings).

It seems that v.in.ogr is confusing input and output, or it is unfinished work for customized PostGIS import not using OGR. I have effectively deactivated the new get_datasource_name() fn in trunk r71093. v.in.ogr should now work again with OGR syntax, and there is no need to change the GRASS db connection settings.

Markus M

>

> I understand that there are many different situations in databases and that it is difficult to handle them all, but (again: unless I'm completely misunderstanding things) the current handling just seems not only overly complicated, but actually with serious consequences and errors.
>
> And I think we should not move away from ogr in v.in.ogr.
>
> In addition, in the GUI import wizard, after having set db.login and being able to see the available tables with 'v.in.ogr -l', the GUI cannot list these same tables because it seems to attempt to connect to the database 'template1' to which the defined user does not have access rights.
>
> Finally, db.login does not allow erasing existing settings, but the GUI takes its information from the login file when determining where to connect when "database" is chosen in the import wizard. One has to manually erase the file (or relevant lines in the file).
>
> Maybe a v.in.postgis handling special access needs might be a solution, leaving v.in.ogr as a real frontend to ogr with exact same behaviour as ogr ?
>
> Please let me know if I just completely misunderstand the functioning of v.in.ogr for PostGIS layers at this stage...
>
> Moritz
> _______________________________________________
> grass-dev mailing list
> [hidden email]
> https://lists.osgeo.org/mailman/listinfo/grass-dev


_______________________________________________
grass-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS layer import

Martin Landa
Hi,

2017-05-17 15:03 GMT+02:00 Markus Metz <[hidden email]>:

> It seems that v.in.ogr is confusing input and output, or it is unfinished
> work for customized PostGIS import not using OGR. I have effectively
> deactivated the new get_datasource_name() fn in trunk r71093. v.in.ogr
> should now work again with OGR syntax, and there is no need to change the
> GRASS db connection settings.

I will be ale to take a look on Friday. Martin

--
Martin Landa
http://geo.fsv.cvut.cz/gwiki/Landa
http://gismentors.cz/mentors/landa
_______________________________________________
grass-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS layer import

Markus Neteler
In reply to this post by Markus Metz-3
On Wed, May 17, 2017 at 3:03 PM, Markus Metz
<[hidden email]> wrote:
> On Wed, May 17, 2017 at 9:31 AM, Moritz Lennert
...
>> I would, therefore, expect to just be able to do this:
>>
>> v.in.ogr "PG:host=myhost user=theuser password=thepassword
>> dbname=thedatabase" layer=theschema.thetable out=mygrassmap
>
> I agree

Just a side-note: I would be very happy to not store the password in
the cmd line history.
PG itself supports a password file
(https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html).

Please keep any password hiding option(s) in mind when working on the design.

thanks,
markusN
_______________________________________________
grass-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-dev
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: PostGIS layer import

Markus Metz-3


On Thu, May 18, 2017 at 12:59 PM, Markus Neteler <[hidden email]> wrote:

>
> On Wed, May 17, 2017 at 3:03 PM, Markus Metz
> <[hidden email]> wrote:
> > On Wed, May 17, 2017 at 9:31 AM, Moritz Lennert
> ...
> >> I would, therefore, expect to just be able to do this:
> >>
> >> v.in.ogr "PG:host=myhost user=theuser password=thepassword
> >> dbname=thedatabase" layer=theschema.thetable out=mygrassmap
> >
> > I agree
>
> Just a side-note: I would be very happy to not store the password in
> the cmd line history.
> PG itself supports a password file
> (https://www.postgresql.org/docs/9.6/static/libpq-pgpass.html).
>
> Please keep any password hiding option(s) in mind when working on the design.

Apparently such a password file is recognized by the PostgreSQL client library, therefore no special mechanism should be needed for OGR or GRASS, but a hint in the manuals of db.login and v.in.ogr might be helpful.

Markus M

_______________________________________________
grass-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/grass-dev
Loading...