headers
Kevin Neufeld | 1 Sep 2009 01:32
Favicon

Re: union/merge huge river dataset into single lines group by name

The below queries should group together all rivers with the same name into a MULTILINESTRING geometry. 
Since you are 
using LineMerge, degree 2 nodes, are removed from every collection.  The subsequent query will expand the
collections 
back to individual linestrings so that disjoint lines (or lines that meet at confluences greater than 2)
remain intact. 
  This is assuming of course that your dataset is properly noded.  If you have cases where rivers cross or where
they 
intersect at vertices other than their endpoints and you want to treat these as one feature, use ST_Union
instead of 
ST_LineMerge.

-- creates MULTILINESTRING per river name
CREATE TABLE merged_rivers AS
SELECT river_name, ST_Multi(ST_LineMerge(ST_Collect(the_geom))) AS geom
FROM my_river_table
GROUP BY river_name

-- explodes the MULTILINESTRING into separate LINESTRINGs
SELECT river_name, (ST_Dump(geom)).geom
FROM merged_rivers;

Hope that helps,
Kevin

karsten vennemann wrote:
> PostGIS users,
>  
> I was researching how to effectively merge river segments in a really 
> huge river dataset that have the same name and touch each other (share a
(Continue reading)

Permalink | Reply | 0 comments |
headers
Steve.Toutant | 1 Sep 2009 16:46
Picon

several SRID on one table


Hello,
We need to use a table for several purposes with different SRID.
Is it a good practice to have several geometry columns on one table or should we create one table per SRID?
What are the pros and cons of using several geometry columns on one table?

thanks
Steve

Steve Toutant, M. Sc.
Analyste en géomatique
Secteur environnement
Direction des risques biologiques, environnementaux et occupationnels
Institut national de santé publique du Québec
945, avenue Wolfe
Québec, Qc G1V 5B3

Tél.: (418) 650-5115 #5281
Fax.: (418) 654-3144
steve.toutant <at> inspq.qc.ca
http://www.inspq.qc.ca

  

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 12 comments |
headers
Gady Pitaru | 1 Sep 2009 17:15
Picon

Return row from point

Is there a function I can use that will return the corresponding row values of the geometry that intersects a passed in point? So if I pass in the X, Y, and WKID or SRID, the function will hand back the row(s) from the database that have geometry that intersect that point. Any help is much appreciated.

Thanks,
Gady

_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 1 comment |
headers
Daniel Grum | 1 Sep 2009 17:29
Picon
Favicon

How cutting out polygon

Hi.

How I can cut 4 or more differnt polygon geometries out of one big 
geomtry[MULTIPOLYGON].

I have one polygon (saved in the schema.tabel.column:  
public.fog_of_war.the_geom)
and from this I want to cut out  the squares, that I create with:

SELECT 
ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom, 5000))
FROM public.grenze_by grenze_by, public.holzfaeller holzfaeller 
WHERE grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));

I don't want to save this cut out information in an extra table, I only 
want to get a fog_of_war layer.
And You only can see what is behind this Layer(than you see the map and 
bulidings) where the layer was cut out.

Can I solve this problem with UPDATE like this:

UPDATE fog_of_war
SET the_geom = (    SELECT 
ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom, 5000))
                                FROM public.grenze_by grenze_by, 
public.holzfaeller holzfaeller 
                                WHERE grenze_by.the_geom && 
ST_Expand(holzfaeller.the_geom, 5000));

I ask because I get the message: "FEHLER:  als Ausdruck verwendete 
Unteranfrage ergab mehr als eine Zeile"

Thanks in advance.

--Daniel
Permalink | Reply | 3 comments |
headers
Kevin Neufeld | 1 Sep 2009 17:33
Favicon

Re: Return row from point

Try ST_Intersects.
http://postgis.refractions.net/documentation/manual-svn/ST_Intersects.html

SELECT *
FROM my_spatial_table
WHERE ST_Intersects(
   the_geom,
   ST_SetSrid(ST_MakePoint(1107983 739378), 2927)
);

Gady Pitaru wrote:
> Is there a function I can use that will return the corresponding row 
> values of the geometry that intersects a passed in point? So if I pass 
> in the X, Y, and WKID or SRID, the function will hand back the row(s) 
> from the database that have geometry that intersect that point. Any help 
> is much appreciated.
> 
> Thanks,
> Gady
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Pavel Iacovlev | 1 Sep 2009 17:34
Picon

Re: several SRID on one table

The cons I think are that some 3rd party apps may get confused then
working with PostGIS as they expect 1 geometry column per table, old
version of QGIS have this problem (I may be wrong).

In general you should have NO trouble with dual geometry columns in
your app, I use this approach in production and it works fine and it's
easy to implement.

As a note you can create an INDEX on a geometry column that is a
transformed geometry to specified projection and you should get really
fast geometry on the fly projection transformation.
CREATE INDEX myindex ON mytable USING GIST ( ST_Transform(the_geom, mysrid) );
Don't  forget to run EXPLAINS to see if it actually works.

On Tue, Sep 1, 2009 at 5:46 PM, <Steve.Toutant <at> inspq.qc.ca> wrote:
>
> Hello,
> We need to use a table for several purposes with different SRID.
> Is it a good practice to have several geometry columns on one table or
> should we create one table per SRID?
> What are the pros and cons of using several geometry columns on one table?
>
> thanks
> Steve
>
> Steve Toutant, M. Sc.
> Analyste en géomatique
> Secteur environnement
> Direction des risques biologiques, environnementaux et occupationnels
> Institut national de santé publique du Québec
> 945, avenue Wolfe
> Québec, Qc G1V 5B3
>
> Tél.: (418) 650-5115 #5281
> Fax.: (418) 654-3144
> steve.toutant <at> inspq.qc.ca
> http://www.inspq.qc.ca
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

--

-- 
http://iap.md, The future is open
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 2 comments |
headers
Steve.Toutant | 1 Sep 2009 17:39
Picon

Re: several SRID on one table


Thanks Pavel for your answer.
Do you suggest that I could use one geometry column and create several index instead?

Steve Toutant, M. Sc.
Analyste en géomatique
Secteur environnement
Direction des risques biologiques, environnementaux et occupationnels
Institut national de santé publique du Québec
945, avenue Wolfe
Québec, Qc G1V 5B3

Tél.: (418) 650-5115 #5281
Fax.: (418) 654-3144
steve.toutant <at> inspq.qc.ca
http://www.inspq.qc.ca

 




Pavel Iacovlev <iacovlev.pavel <at> gmail.com> <at> postgis.refractions.net
Envoyé par : postgis-users-bounces <at> postgis.refractions.net

01/09/2009 11:34 AM

Veuillez répondre à
PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
A
 PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
cc
Objet
 Re: [postgis-users] several SRID on one table







The cons I think are that some 3rd party apps may get confused then
working with PostGIS as they expect 1 geometry column per table, old
version of QGIS have this problem (I may be wrong).

In general you should have NO trouble with dual geometry columns in
your app, I use this approach in production and it works fine and it's
easy to implement.

As a note you can create an INDEX on a geometry column that is a
transformed geometry to specified projection and you should get really
fast geometry on the fly projection transformation.
CREATE INDEX myindex ON mytable USING GIST ( ST_Transform(the_geom, mysrid) );
Don't  forget to run EXPLAINS to see if it actually works.

On Tue, Sep 1, 2009 at 5:46 PM, <Steve.Toutant <at> inspq.qc.ca> wrote:
>
> Hello,
> We need to use a table for several purposes with different SRID.
> Is it a good practice to have several geometry columns on one table or
> should we create one table per SRID?
> What are the pros and cons of using several geometry columns on one table?
>
> thanks
> Steve
>
> Steve Toutant, M. Sc.
> Analyste en géomatique
> Secteur environnement
> Direction des risques biologiques, environnementaux et occupationnels
> Institut national de santé publique du Québec
> 945, avenue Wolfe
> Québec, Qc G1V 5B3
>
> Tél.: (418) 650-5115 #5281
> Fax.: (418) 654-3144
> steve.toutant <at> inspq.qc.ca
> http://www.inspq.qc.ca
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>



--
http://iap.md, The future is open
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 1 comment |
headers
Pavel Iacovlev | 1 Sep 2009 17:51
Picon

Re: several SRID on one table

Depends on your update/select ratio. If you have lots of SELECT like
90% and few updates and your just serving your data lets say to
mapserver I would just go with 2 geometry columns iit's easier and
faster.

On Tue, Sep 1, 2009 at 6:39 PM, <Steve.Toutant <at> inspq.qc.ca> wrote:
>
> Thanks Pavel for your answer.
> Do you suggest that I could use one geometry column and create several index
> instead?
>
> Steve Toutant, M. Sc.
> Analyste en géomatique
> Secteur environnement
> Direction des risques biologiques, environnementaux et occupationnels
> Institut national de santé publique du Québec
> 945, avenue Wolfe
> Québec, Qc G1V 5B3
>
> Tél.: (418) 650-5115 #5281
> Fax.: (418) 654-3144
> steve.toutant <at> inspq.qc.ca
> http://www.inspq.qc.ca
>
>
>
>
> Pavel Iacovlev <iacovlev.pavel <at> gmail.com> <at> postgis.refractions.net
> Envoyé par : postgis-users-bounces <at> postgis.refractions.net
>
> 01/09/2009 11:34 AM
>
> Veuillez répondre à
> PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
> A
> PostGIS Users Discussion <postgis-users <at> postgis.refractions.net>
> cc
> Objet
> Re: [postgis-users] several SRID on one table
>
>
>
>
>
>
> The cons I think are that some 3rd party apps may get confused then
> working with PostGIS as they expect 1 geometry column per table, old
> version of QGIS have this problem (I may be wrong).
>
> In general you should have NO trouble with dual geometry columns in
> your app, I use this approach in production and it works fine and it's
> easy to implement.
>
> As a note you can create an INDEX on a geometry column that is a
> transformed geometry to specified projection and you should get really
> fast geometry on the fly projection transformation.
> CREATE INDEX myindex ON mytable USING GIST ( ST_Transform(the_geom, mysrid)
> );
> Don't  forget to run EXPLAINS to see if it actually works.
>
> On Tue, Sep 1, 2009 at 5:46 PM, <Steve.Toutant <at> inspq.qc.ca> wrote:
>>
>> Hello,
>> We need to use a table for several purposes with different SRID.
>> Is it a good practice to have several geometry columns on one table or
>> should we create one table per SRID?
>> What are the pros and cons of using several geometry columns on one table?
>>
>> thanks
>> Steve
>>
>> Steve Toutant, M. Sc.
>> Analyste en géomatique
>> Secteur environnement
>> Direction des risques biologiques, environnementaux et occupationnels
>> Institut national de santé publique du Québec
>> 945, avenue Wolfe
>> Québec, Qc G1V 5B3
>>
>> Tél.: (418) 650-5115 #5281
>> Fax.: (418) 654-3144
>> steve.toutant <at> inspq.qc.ca
>> http://www.inspq.qc.ca
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users <at> postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
>
>
>
> --
> http://iap.md, The future is open
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

--

-- 
http://iap.md, The future is open
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 0 comments |
headers
Pavel Iacovlev | 1 Sep 2009 17:58
Picon

Re: How cutting out polygon

You should translate the error message by the way if you want to get answers :)

I think the problem is that your select returns multiple
rows(geometries) but update expect 1 row. Try putting a
ST_COLLECT/ST_UNION in your query.

UPDATE fog_of_war
SET the_geom = (    SELECT
ST_Union(ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom,
5000)))
                              FROM public.grenze_by grenze_by,
public.holzfaeller holzfaeller                               WHERE
grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));

2009/9/1 Daniel Grum <daniel.grum <at> unibw.de>:
> Hi.
>
> How I can cut 4 or more differnt polygon geometries out of one big
> geomtry[MULTIPOLYGON].
>
> I have one polygon (saved in the schema.tabel.column:
>  public.fog_of_war.the_geom)
> and from this I want to cut out  the squares, that I create with:
>
> SELECT ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom,
> 5000))
> FROM public.grenze_by grenze_by, public.holzfaeller holzfaeller WHERE
> grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));
>
> I don't want to save this cut out information in an extra table, I only want
> to get a fog_of_war layer.
> And You only can see what is behind this Layer(than you see the map and
> bulidings) where the layer was cut out.
>
> Can I solve this problem with UPDATE like this:
>
> UPDATE fog_of_war
> SET the_geom = (    SELECT
> ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom, 5000))
>                               FROM public.grenze_by grenze_by,
> public.holzfaeller holzfaeller                               WHERE
> grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));
>
> I ask because I get the message: "FEHLER:  als Ausdruck verwendete
> Unteranfrage ergab mehr als eine Zeile"
>
> Thanks in advance.
>
> --Daniel
> _______________________________________________
> postgis-users mailing list
> postgis-users <at> postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--

-- 
http://iap.md, The future is open
_______________________________________________
postgis-users mailing list
postgis-users <at> postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Permalink | Reply | 2 comments |
headers
Daniel Grum | 1 Sep 2009 19:04
Picon
Favicon

Re: How cutting out polygon

Pavel Iacovlev schrieb:
> You should translate the error message by the way if you want to get answers :)
>
> I think the problem is that your select returns multiple
> rows(geometries) but update expect 1 row. Try putting a
> ST_COLLECT/ST_UNION in your query.
>
> UPDATE fog_of_war
> SET the_geom = (    SELECT
> ST_Union(ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom,
> 5000)))
>                               FROM public.grenze_by grenze_by,
> public.holzfaeller holzfaeller                               WHERE
> grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));
>
> 2009/9/1 Daniel Grum <daniel.grum <at> unibw.de>:
>   
>> Hi.
>>
>> How I can cut 4 or more differnt polygon geometries out of one big
>> geomtry[MULTIPOLYGON].
>>
>> I have one polygon (saved in the schema.tabel.column:
>>  public.fog_of_war.the_geom)
>> and from this I want to cut out  the squares, that I create with:
>>
>> SELECT ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom,
>> 5000))
>> FROM public.grenze_by grenze_by, public.holzfaeller holzfaeller WHERE
>> grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));
>>
>> I don't want to save this cut out information in an extra table, I only want
>> to get a fog_of_war layer.
>> And You only can see what is behind this Layer(than you see the map and
>> bulidings) where the layer was cut out.
>>
>> Can I solve this problem with UPDATE like this:
>>
>> UPDATE fog_of_war
>> SET the_geom = (    SELECT
>> ST_Intersection(grenze_by.the_geom,ST_Expand(holzfaeller.the_geom, 5000))
>>                               FROM public.grenze_by grenze_by,
>> public.holzfaeller holzfaeller                               WHERE
>> grenze_by.the_geom && ST_Expand(holzfaeller.the_geom, 5000));
>>
>> I ask because I get the message: "FEHLER:  als Ausdruck verwendete
>> Unteranfrage ergab mehr als eine Zeile"
>>
>> Thanks in advance.
>>
>> --Daniel
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users <at> postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>     
>
>
>
>   
I have edited my SQL

The problem now is, that only the first row  will be regarded in this:

UPDATE fog_of_war
SET the_geom = ST_BuildArea(St_Collect(grenze_by.the_geom, 
ST_Expand(holzfaeller.the_geom, 3000)))
FROM public.grenze_by grenze_by, public.holzfaeller holzfaeller;

How I can regard all rows of the table in a UPDATE query --> because 
ST_Union or ST_Collect don't work in a UPDATE query

--Daniel
Permalink | Reply | 1 comment |

Gmane