[postgis] idea for postgis application & Implications for GEOMETRY_COLUMNS

John Reid jgreid at uow.edu.au
Tue Mar 19 05:17:10 PST 2002

Just a quick thought.  What about storing the geometry for the layer (in 
a seperate table?) at a reduced resolution?   Similar to the concept of 
weeding.  I think this is the right term - essentially using a sliding 
window to store only the points that fall outside the tolerance when the 
line curves.  Anyone know of a tool that could do this?  I think the 
ESRI algorithm just weeds points less than a specified distance from the 
previous point, but I could be wrong about this (haven't used ArcInfo 
much - I'm a sysadmin).   In any case this second approach would 
probably work fine for display purposes until a filtering solution could 
be implemented if the first option is a better one.

This approach could also be used to store multiple coordinate systems if 
desired.  Another alternative might be to rework the internal storage 
structures for geometry type (I know which alternative I prefer, for the 
time being anyway ;-).

As far as table structures go, something along the lines of (this needs 
more thought, but the general idea should be apparent.  Syntax is not 
right either):

CREATE TABLE "a_feature_layer_data" (
    "f_id" unique_id,
    "f_name" text,
    "attr1"    atype,
    attr2 etc....

/* geometry storage kludge */
CREATE TABLE "a_feature_layer_geom"
    "f_id" unique_id,
    "min_scale" integer, /* original object = 0 (potential problems with 
x/0 ?) - attribute needed if clustering used */
    "the_geom" geometry,

/* better? Really wish PostgreSQL supported SQL:1999 ADT's here */
CREATE TABLE "a_feature_layer_geom"
    "f_id" unique_id,
    "the_geom-25000" geometry,
    "the_geom-1000000" geometry,

/* accessed for a particular scale as... */
CREATE VIEW "a_feature_layer" AS .... aflg.the_geom-25000

CLUSTER geom_scale_index ON a_feature_layer_geom;

The other alternative to cluster (this does some nasty things to 
permissions and indexes - they are actually lost, assuming the docs are 
correct) or multiple geometry columns is to have a seperate 
feature_layer_geom table for each scale.  However this means that a 
mapping from layer to geometry table is needed.  To me the logical place 
for the extra information is in geometry_columns.

If this approach is taken, it would make sense to implement 
geometry_columns as a view rather than a table (the alternative is to 
inherit a pgis table from geometry_columns - not good in this instance 
due to default behaviour of select - this can only be set manually per 
session or globally for *all* databases).

This needs more thought, there might be a better way to do this - 
especially as the "pgis_features" table is suspiciously looking like the 
start of support for "catalogue services" (GSDI cookbook Ch 4. 
http://www.gsdi.org/ ).  I'm currently trying to find out more about the 
relationship between the draft ISO metadata and catalogue standards (DIS 
19115 and 19110), OGC Catalog implementation specifications and 
apparently there is also (related?) development of catalogs or 
registries in the OGC Web Map/Feature Server efforts.  I'll report back 
on this when I'm less confused....

CREATE TABLE "pgis_features" (
    "f_layer_id" unique_id,
    "f_layer_name" character varying(256) NOT NULL,
    "f_layer_desc" text,
    "f_table_catalog" character varying(256) NOT NULL,
    "f_table_schema" character varying(256) NOT NULL,
    "f_table_name" character varying(256) NOT NULL

CREATE TABLE "pgis_geometry_columns" (
    "f_table_catalog" character varying(256) NOT NULL,
    "f_table_schema" character varying(256) NOT NULL,
    "f_table_name" character varying(256) NOT NULL,
    "f_layer_id" uuid NOT NULL,  /* how to get around insert problem if 
OGC compliant functions used to populate the table ?*/
    "f_geometry_column" character varying(256) NOT NULL,
    "coord_dimension" integer NOT NULL,
    "srid" integer NOT NULL,
    "type" character varying(30) NOT NULL,
    "is_proxy" integer NOT NULL,  /* original object = 0 (default?), 
other codes for ??? */
    "min_display_scale" integer, /* original object = 0 (potential 
problems with x/0 ?)*/
    "max_display_scale" integer, /* necessary? - if so better handled in 
table defining layers for map views? */
    Constraint "gc_pk" Primary Key ("f_table_catalog", "f_table_schema", 
"f_table_name", "f_geometry_column")

CREATE VIEW "geometry_columns" AS ....(OGC spec).

Eeek.  What a mess.  Hope it's not too confusing.  I'll look at this 
again after some sleep....

Nedjo Rogers wrote:

>Alternately or to supplement some sort of vertex filtering (e.g., an
>implementation of the Douglas-Peucker line simplification algorithm), a
>simple clent-side approach would be to define minimum and maximum display
>scales for large data sets and load them selectively (i.e., load only those
>features intersecting bounding box), perhaps using a server-side session log
>to avoid sending any one feature twice in a session.
>If there aren't good reasons otherwise, it would be good to see client
>software built around the OpenGIS web feature server specification, i.e.,
>querying and loading according to the xml specs given there, e.g., using
>Native browser support for SVG is seemingly still a ways off, but in the
>meantime using the Adobe SVG plugin does offer possibilities.  I'm currently
>looking at an approach that would use DOM and the Adobe functions parseXML
>and postURL to post queries to a WFS and parse the results, drawing on
>approaches I've sketched in in my opensvgmapserver.  It turns out that
>parsing GML into SVG using DOM is relatively straighforward, as SVG is so
>much simpler (e.g., concatenates all path rings into one string,
>irrespective of whether they're "inner" or "outer" rings or what polygon
>they belong to).  E.g., for polygon or linestring features:
>then cycle through the coords nodes putting them in path format and
>concatenating them into a string to be the "d" attribute of a new path.
>Nedjo Rogers
>>-----Original Message-----
>>From: Tyler Mitchell [mailto:tmitchell at lignum.com]
>>Sent: March 13, 2002 4:48 PM
>>To: postgis at yahoogroups.com
>>Subject: Re: [postgis] idea for postgis application
>>Paul wrote:
>>>Any interactive viewer would be sweeeeet, although Mac-only might be a
>>>bummer for linux'ers like myself. The main issue I forsee w/ interactive
>>>viewers is avoiding the "crushing load" problem when people are looking
>>>at large extents on large large tables. (All streams in British
>>>Columbia, for example.)
>>Is it realistic to think on doing some sort of on-the-fly filtering of
>>features to generalize them depending on scale of the query?  Just a
>>though/question for your Paul.  Perhaps a stored function (right
>>terminology?) that would filter out various vertex is desired?  I was
>>originally thinking this would reduce the "load" at the client end but I
>>guess there would be a server-side processing load increase eh?
>>To unsubscribe from this group, send an email to:
>>postgis-unsubscribe at yahoogroups.com
>>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>To unsubscribe from this group, send an email to:
>postgis-unsubscribe at yahoogroups.com
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 

john reid                                    e-mail: jgreid at uow.edu.au
technical officer                               building 41, room G02A 
school of geosciences                          phone: +61 02 4221 3963
university of wollongong                         fax: +61 02 4221 4250
nsw  2522  australia
uproot your questions from their ground and the dangling roots will be
seen.  more questions!
                                                       -mentat zensufi

apply standard disclaimers as desired...

------------------------ Yahoo! Groups Sponsor ---------------------~-->
Tiny Wireless Camera under $80!
Order Now! FREE VCR Commander!
Click Here - Only 1 Day Left!

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com


Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 

More information about the postgis-users mailing list