headers
Boehm, Andreas | 1 Aug 2007 11:42
Picon

Retrieve all tables and views with geometry columns

Hi list,

I would like to list all the geometry columns in a database. So the user
can select the features he or she wants to see. Therefore I need to
retrieve the database's metadata. 
With "select * from geometry_columns" I'm able to get a list of all
tables with geometry columns. But I don't get information about the
_views_ with a geometry column. 
Do I have to parse the definition text in pg_views? Maybe there is an
easier way...

Thanks
Andreas

P. S. Performance analyze with 250.000.000 points is still pending. I'll
be back if I have further questions or results.
Permalink | Reply | 3 comments |
headers
Michael Fuhr | 1 Aug 2007 12:59
Favicon

Re: Retrieve all tables and views with geometry columns

On Wed, Aug 01, 2007 at 11:42:15AM +0200, Boehm, Andreas wrote:
> I would like to list all the geometry columns in a database. So the user
> can select the features he or she wants to see. Therefore I need to
> retrieve the database's metadata. 
> With "select * from geometry_columns" I'm able to get a list of all
> tables with geometry columns. But I don't get information about the
> _views_ with a geometry column. 
> Do I have to parse the definition text in pg_views? Maybe there is an
> easier way...

You could query pg_catalog.pg_attribute or information_schema.columns.

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html

SELECT n.nspname, c.relname, a.attname
  FROM pg_attribute AS a
  JOIN pg_class AS c ON c.oid = a.attrelid
  JOIN pg_namespace AS n ON n.oid = c.relnamespace
 WHERE a.atttypid = 'geometry'::regtype
   AND NOT a.attisdropped
   AND c.relkind IN ('r', 'v')
 ORDER BY n.nspname, c.relname, a.attname;

or

SELECT table_schema, table_name, column_name
  FROM information_schema.columns
 WHERE udt_name = 'geometry'
 ORDER BY table_schema, table_name;
(Continue reading)

Permalink | Reply | 0 comments |
headers
Obe, Regina | 1 Aug 2007 13:06
Favicon

RE: Retrieve all tables and views with geometry columns

I usually just manually add my views into the geometry_columns table.
Parsing could be done, but could slow things down enough to not be
useful depending on how many tables you've got.

I heard some talk of in future releases having a trigger that
automatically updates the geometry_columns table including views.

Hope that helps,
Regina
 

-----Original Message-----
From: postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net] On Behalf Of
Boehm, Andreas
Sent: Wednesday, August 01, 2007 5:42 AM
To: postgis-users <at> postgis.refractions.net
Subject: [postgis-users] Retrieve all tables and views with geometry
columns

Hi list,

I would like to list all the geometry columns in a database. So the user
can select the features he or she wants to see. Therefore I need to
retrieve the database's metadata. 
With "select * from geometry_columns" I'm able to get a list of all
tables with geometry columns. But I don't get information about the
_views_ with a geometry column. 
Do I have to parse the definition text in pg_views? Maybe there is an
easier way...
(Continue reading)

Permalink | Reply | 0 comments |
headers
Armin Burger | 1 Aug 2007 13:10
Picon

Re: Retrieve all tables and views with geometry columns

Hi Andreas

I used something like

SELECT c.relname 
FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n 
WHERE a.attrelid=c.oid 
  AND a.atttypid=t.oid 
  AND t.typname='geometry' 
  AND (c.relkind = 'r' OR c.relkind = 'v') 
  AND c.relnamespace = n.oid

to get all tables and views with geometry columns. 

armin

-------- 
Subject: Retrieve all tables and views with geometry columns

> Hi list,
> 
> I would like to list all the geometry columns in a database. So the user
> can select the features he or she wants to see. Therefore I need to
> retrieve the database's metadata. 
> With "select * from geometry_columns" I'm able to get a list of all
> tables with geometry columns. But I don't get information about the
> _views_ with a geometry column. 
> Do I have to parse the definition text in pg_views? Maybe there is an
> easier way...
>
(Continue reading)

Permalink | Reply | 0 comments |
headers
Obe, Regina | 1 Aug 2007 13:20
Favicon

Generic Solution to Nearest Neighbor problem

I'm hoping this will be of some benefit to others.  I've read a lot of posts about people trying solve various nearest neighbor like problems.  After running into this stumbling block myself and using various unsatisfying workarounds for it, I think I have finally stumbled across a decent solution that is fairly cookie cutter and it is faster than others I have tried in the past.  I haven't stress tested it yet though.
 
I've documented the details of it here if anyone is interested.
 
 
Thanks,
Regina
 
 

The substance of this message, including any attachments, may be confidential, legally privileged and/or exempt from disclosure pursuant to Massachusetts law. It is intended solely for the addressee. If you received this in error, please contact the sender and delete the material from any computer. 

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
wirote la | 2 Aug 2007 11:16
Picon

How I can split multilinestring to exact distance?

Dear list,

I am very new for postgis but I need your help, I have a set of road data in multilinestring, and need to split to sub linestring for 1KM each or less than if last segment.

I try to search for solve my problem for a week ready but still can not get.

My system on WinXP Postgis version 1.1.6.

Thank in advanced

--
Wirote Laongmanee

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Michael Fuhr | 2 Aug 2007 12:29
Favicon

Re: problem with shp2pgsql / psql import

On Tue, Jul 31, 2007 at 11:58:32AM +0200, Stefan Zweig wrote:
> i have attached an excerpt of the _g2793.sql which creates the
> table and imports one geometry row. maybe anybody can try importing
> this in his/her postgis and see whether postgis gives as well a
> null geometry after
> 
> select id, name, astext(the_geom) from _g2793

Are you sure the geometry is null?  What does the following query
return?

SELECT id, name, the_geom IS NULL, length(astext(the_geom)), summary(the_geom) FROM _g2793;

I get this:

       id        |   name   | ?column? | length |             summary              
-----------------+----------+----------+--------+----------------------------------
 632760049010583 | Saarland | f        | 100818 | 
                                                : MultiPolygon[BS] with 1 elements
                                                :   Polygon[] with 1 rings
                                                :    ring 0 has 5294 points
                                                : 
(1 row)

What client are you using?  I see the astext() output in psql but
not in pgAdmin, perhaps because the value is too long.

> i have no idea what is wrong with the INSERT statement. is there
> something like a geometry/WKT-validator?

Nothing is wrong with the statement as far as I can tell.  Try psql
if you aren't using it already.

--

-- 
Michael Fuhr
Permalink | Reply | 0 comments |
headers
Stefan Zweig | 2 Aug 2007 12:48
Picon

Re: problem with shp2pgsql / psql import

hi frank,

you are right. meanwhile i noticed by myself that indeed it was a problem with my client pgAdminIII. the rows
just seemed to long. it seems that the pgAdmindriver has a 64k limit when retrieving rows from postgre.

the querys worked fine when using phppgadmin or psql.

thank you anyway.

regards. stefan

> -----Ursprüngliche Nachricht-----
> Von: PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
> Gesendet: 02.08.07 12:31:21
> An: PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
> Betreff: Re: [postgis-users] problem with shp2pgsql / psql import

> 
> On Tue, Jul 31, 2007 at 11:58:32AM +0200, Stefan Zweig wrote:
> > i have attached an excerpt of the _g2793.sql which creates the
> > table and imports one geometry row. maybe anybody can try importing
> > this in his/her postgis and see whether postgis gives as well a
> > null geometry after
> > 
> > select id, name, astext(the_geom) from _g2793
> 
> Are you sure the geometry is null?  What does the following query
> return?
> 
> SELECT id, name, the_geom IS NULL, length(astext(the_geom)), summary(the_geom) FROM _g2793;
> 
> I get this:
> 
>        id        |   name   | ?column? | length |             summary              
> -----------------+----------+----------+--------+----------------------------------
>  632760049010583 | Saarland | f        | 100818 | 
>                                                 : MultiPolygon[BS] with 1 elements
>                                                 :   Polygon[] with 1 rings
>                                                 :    ring 0 has 5294 points
>                                                 : 
> (1 row)
> 
> What client are you using?  I see the astext() output in psql but
> not in pgAdmin, perhaps because the value is too long.
> 
> > i have no idea what is wrong with the INSERT statement. is there
> > something like a geometry/WKT-validator?
> 
> Nothing is wrong with the statement as far as I can tell.  Try psql
> if you aren't using it already.
> 
> -- 
> Michael Fuhr
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> 

__________________________________________________________________________
Erweitern Sie FreeMail zu einem noch leistungsstärkeren E-Mail-Postfach!		
Mehr Infos unter http://produkte.web.de/club/?mc=021131
Permalink | Reply | 2 comments |
headers
Milo van der Linden | 2 Aug 2007 22:21
Picon
Gravatar

postGIS wfs layer won't work

Hello list,

(cross posted to mapserver-users and postgis-users)

I have a postGIS table that contains cities. I am trying to get this running through mapserver WFS.

Mapserver WFS doesn't generate errors, but returns no data at all.

A postgres getsrid tells me the srid is 4326, which is also in my geometry_columns
qGIS shows the postGIS table direct from the database without problems.

http://82.176.214.209/cgi-bin/mapserv.exe?map=/ms4w/apps/mappingworlds/service/wfs.map&service=WFS&REQUEST=getfeature&typename=worldcities_x&version=1.0.0

What am I doing wrong?
--
3DSite

Milo van der Linden
mlinden <at> zeelandnet.nl
milovanderlinden <at> gmail.com
milo <at> 3dsite.nl
http://www.3dsite.nl

  

De informatie in dit bericht reflecteerd mijn persoonlijke mening en niet die van een bedrijf of instantie. Aan de informatie kunnen geen rechten worden ontleend. Indien dit bericht onderdeel is van een forum, mailing-list of community dan gelden automatisch de bijbehorende voorwaarden.

 
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Thorsten Kraus | 3 Aug 2007 10:23
Picon

AW: Retrieve all tables and views with geometrycolumns

Hello,

I would like to drop all tables from my database which contain geometry columns. Does someone have a
solution for this?

Regards,
Thorsten

-----Ursprüngliche Nachricht-----
Von: postgis-users-bounces <at> postgis.refractions.net
[mailto:postgis-users-bounces <at> postgis.refractions.net]Im Auftrag von
Michael Fuhr
Gesendet: Mittwoch, 1. August 2007 13:00
An: PostGIS Users Discussion
Betreff: Re: [postgis-users] Retrieve all tables and views with
geometrycolumns

On Wed, Aug 01, 2007 at 11:42:15AM +0200, Boehm, Andreas wrote:
> I would like to list all the geometry columns in a database. So the user
> can select the features he or she wants to see. Therefore I need to
> retrieve the database's metadata. 
> With "select * from geometry_columns" I'm able to get a list of all
> tables with geometry columns. But I don't get information about the
> _views_ with a geometry column. 
> Do I have to parse the definition text in pg_views? Maybe there is an
> easier way...

You could query pg_catalog.pg_attribute or information_schema.columns.

http://www.postgresql.org/docs/8.2/interactive/catalog-pg-attribute.html
http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html

SELECT n.nspname, c.relname, a.attname
  FROM pg_attribute AS a
  JOIN pg_class AS c ON c.oid = a.attrelid
  JOIN pg_namespace AS n ON n.oid = c.relnamespace
 WHERE a.atttypid = 'geometry'::regtype
   AND NOT a.attisdropped
   AND c.relkind IN ('r', 'v')
 ORDER BY n.nspname, c.relname, a.attname;

or

SELECT table_schema, table_name, column_name
  FROM information_schema.columns
 WHERE udt_name = 'geometry'
 ORDER BY table_schema, table_name;

--

-- 
Michael Fuhr
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 3 comments |

Gmane