PostGis layers

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

PostGis layers

Charles F. I. Savage
Hi everyone - working with OGR and PostGis.

When OGR opens a Postgis enabled database, it finds its layers via this
query (I added the select g.f_table_name part to show a problem below):

SELECT c.relname, g.f_table_name FROM pg_class c, geometry_columns g
WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg'
AND c.relname ~ g.f_table_name)

I've pasted the results of this query below.  Note that the
polygoncorrections and polgyoneconomics layer show up twice!  This is
caused by this part of the query:

AND c.relname ~ g.f_table_name)

Shouldn't it be a direct match without a regular expression, like this:

AND c.relname = g.f_table_name)


Charlie


"altname";"altname"
"arealandmarks";"arealandmarks"
"arealandmarks";"landmarks"
"completechain";"completechain"
"entitynames";"entitynames"
"featureids";"featureids"
"idhistory";"idhistory"
"landmarks";"landmarks"
"overunder";"overunder"
"pip";"pip"
"polychainlink";"polychainlink"
"polygon";"polygon"
"polygoncorrections";"polygon"
"polygoncorrections";"polygoncorrections"
"polygoneconomic";"polygon"
"polygoneconomic";"polygoneconomic"
"tlidrange";"tlidrange"
"zerocellid";"zerocellid"
"zipcodes";"zipcodes"
"zipplus4";"zipplus4"



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

smime.p7s (3K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: PostGis layers

Frank Warmerdam
On 9/22/05, Charles F. I. Savage <[hidden email]> wrote:

> Hi everyone - working with OGR and PostGis.
>
> When OGR opens a Postgis enabled database, it finds its layers via this
> query (I added the select g.f_table_name part to show a problem below):
>
> SELECT c.relname, g.f_table_name FROM pg_class c, geometry_columns g
> WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg'
> AND c.relname ~ g.f_table_name)
>
> I've pasted the results of this query below.  Note that the
> polygoncorrections and polgyoneconomics layer show up twice!  This is
> caused by this part of the query:
>
> AND c.relname ~ g.f_table_name)
>
> Shouldn't it be a direct match without a regular expression, like this:
>
> AND c.relname = g.f_table_name)

Charlie,

I believe you are corrected.  I have committed this change.

Best regards,
--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, [hidden email]
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent

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

Re: PostGis layers

Oleg Semykin
Frank Warmerdam wrote:

> On 9/22/05, Charles F. I. Savage <[hidden email]> wrote:
>  
>
>> Hi everyone - working with OGR and PostGis.
>>
>> When OGR opens a Postgis enabled database, it finds its layers via this
>> query (I added the select g.f_table_name part to show a problem below):
>>
>> SELECT c.relname, g.f_table_name FROM pg_class c, geometry_columns g
>> WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg'
>> AND c.relname ~ g.f_table_name)
>>
>> I've pasted the results of this query below.  Note that the
>> polygoncorrections and polgyoneconomics layer show up twice!  This is
>> caused by this part of the query:
>>
>> AND c.relname ~ g.f_table_name)
>>
>> Shouldn't it be a direct match without a regular expression, like this:
>>
>> AND c.relname = g.f_table_name)
>>  
>
>
> Charlie,
>
> I believe you are corrected.  I have committed this change.
>
> Best regards,
> --
> ---------------------------------------+--------------------------------------
>
> I set the clouds in motion - turn up   | Frank Warmerdam,
> [hidden email]
> light and sound - activate the windows | http://pobox.com/~warmerdam
> and watch the world go round - Rush    | Geospatial Programmer for Rent
>
> _______________________________________________
> Gdal-dev mailing list
> [hidden email]
> http://lists.maptools.org/mailman/listinfo/gdal-dev
>
>  
>

Hi, Frank

But what about older PostreSQL 7.X version support:

"Unable to identify an operator '=' for types 'name' and 'varchar'
You will have to retype this query using an explicit cast"

See: http://lists.maptools.org/pipermail/gdal-dev/2005-August/006098.html


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

Re: PostGis layers

Frank Warmerdam
On 9/23/05, Oleg Semykin <[hidden email]> wrote:
> Hi, Frank
>
> But what about older PostreSQL 7.X version support:
>
> "Unable to identify an operator '=' for types 'name' and 'varchar'
> You will have to retype this query using an explicit cast"
>
> See: http://lists.maptools.org/pipermail/gdal-dev/2005-August/006098.html

Oleg,

Doh!   Is there a way of explicitly casting that will work fine on old
and newer without having to use the ~ operator?  How about:

 select c.relname from pg_class c, geometry_columns g where (c.relkind
in ('r','v') AND c.relname !~ '^pg' AND c.relname::text =
g.f_table_name::text);

That seems to work on my 7.1 database and my 8.x database.  I'll
commit that.

Best regards,

--
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, [hidden email]
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent

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

Re: PostGis layers

Charles F. I. Savage
In reply to this post by Charles F. I. Savage
I suppose its worth mentioning that this still leaves the problem of
having the same table name in two different schemas - say I have a table
called completechain in a tiger_2004 schema and one in a tiger_2002
schema.  I know that there is already a bug about postgresql schemas,
but no fix yet.

I think there is an argument that the layer name should not be
"completechain" but instead should be "tiger_2004.completechain" or
"tiger_2002.completechain" or the default "public.completechain."

Otherwise, if I write dataSource.getLayerByName("completechain") then
what happens?  I get the first layer named "completechain" I'd guess but
obviously that's not a very good solution.

I think you could probably make this backward compatible by tacking on
"public." to a layer name when a user calles getLayerName without
specifying a schema.  Thus dataSource.getLayerByName("completechain")
becomes dataSource.getLayerByName("public.completechain").

An alternative approach is to set the "search_path" when ogr logs into
postgresql (as mentioned in the bug report), but I don't see how that
can solve the problem of the same table name in multiple schemas.


Thanks,

Charlie




Charles F. I. Savage wrote:

> Hi everyone - working with OGR and PostGis.
>
> When OGR opens a Postgis enabled database, it finds its layers via this
> query (I added the select g.f_table_name part to show a problem below):
>
> SELECT c.relname, g.f_table_name FROM pg_class c, geometry_columns g
> WHERE (c.relkind in ('r','v') AND c.relname !~ '^pg'
> AND c.relname ~ g.f_table_name)
>
> I've pasted the results of this query below.  Note that the
> polygoncorrections and polgyoneconomics layer show up twice!  This is
> caused by this part of the query:
>
> AND c.relname ~ g.f_table_name)
>
> Shouldn't it be a direct match without a regular expression, like this:
>
> AND c.relname = g.f_table_name)
>
>
> Charlie
>
>
> "altname";"altname"
> "arealandmarks";"arealandmarks"
> "arealandmarks";"landmarks"
> "completechain";"completechain"
> "entitynames";"entitynames"
> "featureids";"featureids"
> "idhistory";"idhistory"
> "landmarks";"landmarks"
> "overunder";"overunder"
> "pip";"pip"
> "polychainlink";"polychainlink"
> "polygon";"polygon"
> "polygoncorrections";"polygon"
> "polygoncorrections";"polygoncorrections"
> "polygoneconomic";"polygon"
> "polygoneconomic";"polygoneconomic"
> "tlidrange";"tlidrange"
> "zerocellid";"zerocellid"
> "zipcodes";"zipcodes"
> "zipplus4";"zipplus4"
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Gdal-dev mailing list
> [hidden email]
> http://lists.maptools.org/mailman/listinfo/gdal-dev


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

smime.p7s (3K) Download Attachment