Re: [postgis-users] Reducing the number of points in a GPS track

[Birgit Laggner]

Hi Alexandre,

perhaps you could run ST_Simplify in a function with a while loop 
testing over ST_NPoints:


CREATE OR REPLACE FUNCTION simplify_npoints(geometry, integer)
  RETURNS geometry AS
$BODY$

DECLARE
  InGeom alias for $1;
  maxpoints alias for $2;
  npoints integer;
  tolerance float;
  outGeom geometry;

Begin

npoints:=ST_NPoints(InGeom);
outGeom:=InGeom;
tolerance:=0.0;

while npoints > maxpoints loop
  tolerance:=tolerance + 0.001;
  outGeom:=ST_Simplify(InGeom, tolerance);
  npoints:=ST_NPoints(outGeom);
end loop;

return outGeom;

End;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION simplify_npoints(geometry, integer) OWNER TO postgres;



I don't know if it will work (didn't test). And perhaps bigger iteration 
steps would be sufficient, too...


Good luck! Regards,

Birgit.


Am 24.10.2012 14:29, schrieb Alexandre Saunier:

Hello.

I would like to know if some tools are available in PostGIS to
simplify a linestring to a given maximum number of points.
A bit like the "simplify,count=<...>" filter in GPSBabel:
http://www.gpsbabel.org/htmldoc-development/filter_simplify.html

ST_Simplify is close to what I need but I don't know in advance what
tolerance to use, only the final maximal number of points.

Is there a way to do that kind of simplification with PostGIS?

Thanks!
Alexandre
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ArcGIS UNION for PostGIS - Reloaded

[Birgit Laggner]

Hi Yesid,

perhaps it would make sense to split the query after the creation of the 
pointonsurface points and to put a CREATE INDEX on the pointonsurface 
points and the wkb_geometries in between. I could imagine the left joins 
would be much faster then.


Good luck,

Birgit.


Am 22.10.2012 18:09, schrieb Yesid Carrillo Vega:
This question has a long history. Theoretically has good solutions 
that worked for me:

http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables
A good hint for this solution is that input geometries MUST BE SINGLE 
not MULTI. It cost me a few days guessing that:


SELECT a.ogc_fid AS ogc_fid_a, b.ogc_fid AS ogc_fid_b, 
new_polys.wkb_geometry as wkb_geometry

FROM
(
SELECT geom AS wkb_geometry, ST_PointOnSurface(geom) AS pip
FROM ST_Dump
(
(
SELECT ST_Polygonize(wkb_geometry) AS wkb_geometry
FROM
(
SELECT ST_Union(wkb_geometry) AS wkb_geometry
FROM
(
SELECT ST_ExteriorRing(ST_GeometryN(wkb_geometry,1)) AS wkb_geometry
FROM table1
UNION ALL
SELECT ST_ExteriorRing(ST_GeometryN(wkb_geometry,1)) AS wkb_geometry
FROM table2
) AS all_lines
) AS noded_lines
)
)
) AS new_polys
LEFT JOIN table1 a ON ST_Within(new_polys.pip, a.wkb_geometry)
LEFT JOIN table2 b ON ST_Within(new_polys.pip, b.wkb_geometry)


However, applying it into a real world case, give me a painful query 
plan result 158.66..5687848.83:


"Nested Loop Left Join  (cost=158.66..5687848.83 rows=3685449 width=40)"
"  Join Filter: st_within(st_pointonsurface(st_dump.geom), 
a.wkb_geometry)"

"  InitPlan 1 (returns $0)"
"->  Aggregate  (cost=158.65..158.66 rows=1 width=32)"
"  ->  Aggregate  (cost=158.63..158.64 rows=1 width=32)"
"->  Append  (cost=0.00..156.50 rows=850 width=32)"
"  ->  Seq Scan on table1  (cost=0.00..146.09 
rows=809 width=32)"
"  ->  Seq Scan on table2  (cost=0.00..10.41 
rows=41 width=32)"

"  ->  Nested Loop Left Join  (cost=0.00..21033.01 rows=13667 width=36)"
"Join Filter: st_within(st_pointonsurface(st_dump.geom), 
b.wkb_geometry)"
"->  Function Scan on st_dump  (cost=0.00..10.00 rows=1000 
width=32)"

"->  Materialize  (cost=0.00..10.62 rows=41 width=165799)"
"  ->  Seq Scan on table2 b  (cost=0.00..10.41 rows=41 
width=165799)"

"  ->  Materialize  (cost=0.00..150.13 rows=809 width=23296)"
"->  Seq Scan on table1 a  (cost=0.00..146.09 rows=809 
width=23296)"


My data:
table1:879 rows
table2:41 rows

Please some advice to make this query more time friendly, specially 
with st_pointonsurface part.


--
/Yesid Carrillo/




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] St_Area to acres

[Birgit Laggner]

I was looking at spatialreference.org , 
too, and did find EPSG 102686, which has US-foot as projection unit and 
is meant for the same region as 26986...


Regards,

Birgit.

Am 10.07.2012 15:26, schrieb Nathan Gerber:
In looking at EPSG:26986 on spatialreference.org 
 ( 
http://spatialreference.org/ref/epsg/26986/ ) it appears that the 
projection's native units are meters rather than feet. If you divide 
by 4046.85642 (number of square meters in an acre) your acreage 
numbers will come out much closer than they are at present but will 
still be a bit off.

--
Nathan Gerber


On Mon, Jul 9, 2012 at 11:44 PM, Daniel Cole 
> wrote:


I am using the code below:

*Select name, acreage, ST_Area(ST_Transform(geom, 26986))/43560 as
acres from ss.fields*

and getting these results.


nameacreage acres
H 01
15.79

1.50398045317257
H 02
29.02

2.76480120804097
H 03
31.95

3.04337512685165
H 04
34.71

3.30710750534269
H 05
111.6

10.6322788992474




I have another program where I am importing the acreage data from
and I just wanted it to match up in postgis well in case I ever
wanted to calculate it myself.  It seems that I am almost there,
but I am a decimal off and I can't understand that.

Also I am not very familiar with the 26986, but I used it because
I saw it used for sqare feet somewhere else.  Should I be using
anything else?

Thanks,

DC

___
postgis-users mailing list
postgis-users@postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Removed double quotes from column and table names

[Birgit Laggner]

Hi Simon,

I would think RENAME would do what you want:

ALTER TABLE schema."TableName" RENAME TO tablename;

ALTER TABLE schema.tablename RENAME COLUMN "ColumnName" TO columnname;

Hope that helps,

Birgit.


Am 19.03.2012 06:03, schrieb Simon Greener:
Is there any way to remove the double quotes created around a table or 
its column names?
My names do NOT include spaces but do include mixed case. I wish to 
remove the quotes and

lower case all names.
How can this be done?
Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file 
the names are mixed case.
I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to 
tell it to lower case all names.

S

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] change of polygon area due to st_transform

[Birgit Laggner]

Hi Steve,

thanks for your reply. Now, I thought about it, I better understand the 
consequences of using planar projections. My approach to minimize errors 
would therefore be to transform my polygons into a spherical system in 
order to compute their area.


Regards,

Birgit.


Am 15.03.2012 14:45, schrieb Stephen Woodbridge:

On 3/15/2012 7:41 AM, Birgit Laggner wrote:

Dear list,

using the function st_transform() in order to transform polygon
geometries from srid 31468 to 31467, the polygon area of the transformed
geometry is changed compared to the original polygon area . The
percentual change is around 0.1% but with big geometries, it results in
changes of several hectares, which surprises and concerns me a little 
bit.

Can anybody tell me if this is expected behaviour?


Hi Birgit,

Yes, this is expected. The reason that we have lots of transforms in 
the first place is because each transforms has special properties that 
make it unique in some way and allow it to solve this or that problem 
better than some other transform. So for example, some transforms 
preserve area better than others. Some are good approximation at large 
scales and some are better at small scales. So you might want your 
data in one transform for one operation and need to project it into 
another for say area calculation, etc. Beyond that, I'm not sure I can 
recommend which is best for your particular situation.


You might want to construct a square(s) of known size in each 
projection and measure them and then reproject and measure them to get 
a better idea. You have two numbers, How do you know which it 
"correct"? How do you know if either are correct? How is ground truth 
established for your data?


-Steve
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Error in geometry type

[Birgit Laggner]

Hi Yves,

did you test st_geometrytype(st_offsetcurve(way,-20))? Because, I could 
imagine the MultiLineString results from the st_offsetcurve function.


Hope that helps,

Birgit.


Am 04.03.2012 09:07, schrieb yvecai:
I have a strange error on ST_offsetcurve calls on geometries that are 
supposed to be LineStrings.

Yves

gis=# update planet_osm_line set way=ST_OffsetCurve(way,-20) where
osm_id=-1461806;
ERREUR:  Geometry type (MultiLineString) does not match column
type (LineString)

gis=# select geometrytype(way) from planet_osm_line where osm_id =
-1461806;
 geometrytype
--
 LINESTRING
(1 ligne)

gis=# select st_geometrytype(way) from planet_osm_line where
osm_id = -1461806;
 st_geometrytype
-
 ST_LineString
(1 ligne)

gis=# select st_numgeometries(way) from planet_osm_line where
osm_id = -1461806;
 st_numgeometries
--
1
(1 ligne)

See attached the result of:
echo "select st_astext(way) from planet_osm_line where osm_id = 
-1461806;" | psql -d gis > out.wkt






___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Creating lines from a geometry collection

[Birgit Laggner]

Hi David,

I would think working with a subselect would solve your problem:

SELECT
generate_series(1,ST_NumGeometries(geom)) as gid,
geom
FROM (
SELECT
ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) 
As geom,

FROM
points as blade,
lines as g
WHERE
ST_Intersects(g.geom, blade.geom)
) as sel;

Hope that helps,

Birgit.



Am 25.02.2012 22:01, schrieb David Quinn:
After re-reading the documentation I realized I missed an example, so 
I figured out what I need to do (in part):


SELECT
ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom
FROM
points as blade,
lines as g
WHERE
ST_Intersects(g.geom, blade.geom)

This works fine, but I also want to include a column that is an 
identifier. I've tried doing the following:


SELECT
ST_GeomFromEWKB((ST_Dump(ST_Split(g.geom, blade.geom))).geom) As geom,
generate_series(1,ST_NumGeometries((ST_Split(g.geom, 
blade.geom as gid

FROM
points as blade,
lines as g
WHERE
ST_Intersects(g.geom, blade.geom)

While this does generate a series, it starts counting for each line 
that it splits so it is 1,2,3,1,2,1,2,3,4. How can I have a sequential 
GID (or get the total geometry count)?


-David

On Sat, Feb 25, 2012 at 2:54 PM, David Quinn > wrote:


Hello,

I'm trying to split up a line using points into several smaller
lines. I'm using ST_Split but I don't understand how to combine
the returned values into a line. My code is as follows:

SELECT
   ST_Dump(ST_Split(g.geom, blade.geom)) AS geom
FROM
points as blade,
lines as g
WHERE
ST_Intersects(g.geom, blade.geom)

I've tried using a few different geometry constructors such as
ST_LineFromMultiPoint() and ST_GeomFromEWKB() to convert what
ST_Dump() returns to create lines but my syntax/approach is not
correct. What is the correct approach?

Thanks,
David




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ERROR: Error computing path: | SQL state: 38001

[Birgit Laggner]

Hi Palatla,

I only saw in the documentation that the order of arguments in the 
sql-expression used in the function shortest_path_astar is slightly 
different from the one you are using:


This is an example from the documentation 
(http://www.pgrouting.org/docs/1.x/astar.html):


SELECT  *  FROM  shortest_path_astar('SELECT gid AS id, source::int4,
 target::int4, length::double precision AS cost,length::double 
precision
AS reverse_cost, x1, y1, x2, y2 FROM dourol',  3,  7,  true,  true);


As you can see, cost and reverse_cost are placed before the coordinates. 
Perhaps this might be the cause of the problem?


Hope that helps,

Birgit.


Am 22.02.2012 09:41, schrieb Palatla Srikanth:


Hi,

We are developing an web application that users PostgreSQL 8.4 + 
PostGIS1.5  + Pgrouting 1.03 + development using (Java + Struts + 
Hibernate)


*When this Query is executed:*

SELECT ST_AsText(the_geom),id AS Coords FROM delhi_road WHERE id IN 
(SELECT edge_id FROM shortest_path_astar('SELECT id, source, 
target,x1,y1,x2,y2,cost,reverse_cost from delhi_road',550,662,true,true))


We are getting the below mentioned error and the other users accessing 
the application were too facing the database exceptions. Can someone 
help us to fix this issue.


-

*Error from the postgreSQL query*

ERROR:  Error computing path:

** Error **

ERROR: Error computing path:

SQL state: 38001



*Query used:*

**

SELECT ST_AsText(the_geom),id AS Coords FROM delhi_road WHERE id IN 
(SELECT edge_id FROM shortest_path_astar('SELECT id, source, 
target,x1,y1,x2,y2,cost,reverse_cost from delhi_road',550,662,true,true))


-

*Error on the Application server (IDE)*

[ INFO] 46:48 (_MapServiceDAO.java:generateRoute:1525_)

Query is..SELECT ST_AsText(the_geom),id AS Coords FROM delhi_road 
WHERE id IN (SELECT edge_id FROM shortest_path_astar('SELECT id, 
source, target,x1,y1,x2,y2,cost,reverse_cost from 
delhi_road',550,662,true,true))


[ INFO] 46:48 (_MapServiceDAO.java:generateRoute:1552_)

Exception _aala.org.postgresql.util.PSQLException_: ERROR: 
Error computing path: _


Feb 22, 2012 11:46:48 AM org.apache.catalina.core.StandardWrapperValve 
invoke


SEVERE: Servlet.service() for servlet GenerateRoute threw exception

_org.hibernate.SessionException_: Session was already closed

  at org.hibernate.impl.SessionImpl.close(_SessionImpl.java:303_)

  at 
com.tis.hib.util.HibernateSessionFactory.closeSession(_HibernateSessionFactory.java:86_)


  at 
com.tis.dao.impl.MapServiceDAO.generateRoute(_MapServiceDAO.java:1572_)


  at com.tis.servlet.GenerateRoute.doGet(_GenerateRoute.java:125_)

  at javax.servlet.http.HttpServlet.service(_HttpServlet.java:690_)

  at javax.servlet.http.HttpServlet.service(_HttpServlet.java:803_)

  at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(_ApplicationFilterChain.java:290_)


  at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(_ApplicationFilterChain.java:206_)


  at 
org.apache.catalina.core.StandardWrapperValve.invoke(_StandardWrapperValve.java:230_)


  at 
org.apache.catalina.core.StandardContextValve.invoke(_StandardContextValve.java:175_)


  at 
org.apache.catalina.core.StandardHostValve.invoke(_StandardHostValve.java:128_)


  at 
org.apache.catalina.valves.ErrorReportValve.invoke(_ErrorReportValve.java:104_)


  at 
org.apache.catalina.core.StandardEngineValve.invoke(_StandardEngineValve.java:109_)


  at 
org.apache.catalina.connector.CoyoteAdapter.service(_CoyoteAdapter.java:261_)


  at 
org.apache.coyote.http11.Http11Processor.process(_Http11Processor.java:844_)


  at 
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(_Http11Protocol.java:581_)


  at 
org.apache.tomcat.util.net.JIoEndpoint$Worker.run(_JIoEndpoint.java:447_)


  at java.lang.Thread.run(_Thread.java:619_)

*Regards,*

*Palatla Srikanth*




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Landtype minus parcels ?

[Birgit Laggner]

Hi Brian,

you could try this query:

SELECT st_difference(l.wkb_geometry,
 case when st_union(p.wkb_geometry) is null
   then st_collect(p.wkb_geometry)
   else st_union(p.wkb_geometry)
 end)
   as tgeom
from
  big_landtypes l
  inner join
  small_parcels p
  on st_intersects(l.wkb_geometry, p.wkb_geometry)
where l.pkey = 1
group by l.pkey, l.wkb_geometry;

You are getting more than one result per landtype geometry because they 
intersect with more then one parcel geometry. For each intersect case 
there is a result row. You are unioning all parcel geometries which are 
anywhere intersecting any landtype geometry, which is probably resulting 
in a big geometry. I would assume, the proposed query would do better, 
but you would have to try :-) I also added a CASE WHEN with st_collect 
as an alternative for st_union, because, sometimes st_union fails and 
results in a NULL geometry. In this case st_collect could take over and 
as for the st_difference, st_union or st_collect have the same impact.


Hope that helps,

Birgit.



Am 14.02.2012 20:42, schrieb Brian Hamlin:

SELECT distinct on (tgeom)
 st_difference(
   l.wkb_geometry,

   (select st_union(p.wkb_geometry )
   from
 big_landtypes l,
 small_parcels p
   where st_intersects(
 l.wkb_geometry, p.wkb_geometry) AND
 l.pkey = 1)
   ) tgeom
 FROM
   big_landtypes l,
   small_parcels p
 WHERE
   l.pkey = 1
 ORDER BY
   tgeom; 

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Overlapping linestrings

[Birgit Laggner]

Hi Adam,

why do you calculate a buffer for the first linestring? If you only want 
to know if there is any overlapping part of the two linestrings, I would 
expect that ST_Intersects(r1.geo_data,s1.geo_data) would do the job 
best. You could combine it with 
ST_Geometrytype(ST_Intersection(r1.geo_data,s1.geo_data))='ST_LineString' in 
order to to find out if they only cross or really overlap.


Hope that helps,

Birgit.


Am 03.02.2012 21:07, schrieb Adam McManus:
I'm looking for the fastest way to detect whether a given linestring 
overlaps another linestring. Here's my current approach.


Given two tables: r1 and s1, both containing a geo_data column of 
linestring, first narrow the data (taking advantage of gist index) 
with r1.geo_data && s1.geo_data, then use ST_Contains to see if s1 is 
overlapping r1, i.e. r1.geo_data && s1.geo_data 
and ST_Contains(ST_Buffer(r1.geo_data,ST_Length(r1.geo_data)*.002),s1.geo_data)


This approach works, but it's extremely CPU-intensive and slow. Is 
there a better approach?


Thanks,

--
Adam McManus


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Custom Projection

[Birgit Laggner]

Hallo Sean,

I tested it myself and this is because of the export function you are 
using. If I am using pgsql2shp, this results in the following prj:


GEOGCS["Geographic Coordinate System",DATUM["WGS84",SPHEROID["WGS 
84",6378137,298.257223560493,AUTHORITY["Custom","0"]],AUTHORITY["Custom","0"]],PRIMEM["Greenwich",0,AUTHORITY["Custom","0"]],UNIT["degree",0.0174532925199433,AUTHORITY["Custom","0"]],AUTHORITY[""Custom","0"]]


And this would be exactly as you want (the custom authority entries you 
may spare probably...). I don't know, if there is a way to tell QGIS to 
export exactly the coordinate system from the data origin.


Regards,

Birgit.


Am 06.01.2012 18:42, schrieb Sean Christopher Conway:
Hello, thank you for your reply. Yes, essentially I have done this 
exact SQL, but when I export it using quantum gis or FME, i get the 
following projection parameters


Geographic Coordinate System:GCS_WGS_1984
Datum: D_WGS_1984
Prime Meridian: Greenwich
Angular Unit: Degree



whereas, I would like these parameters:

Geographic Coordinate System:Geographic Coordinate System
Datum: WGS84
Prime Meridian: Greenwich
Angular Unit: degree



We need it to be like this and are using the following projection to 
create it:


GEOGCS["Geographic Coordinate 
System",DATUM["WGS84",SPHEROID["WGS84",6378137,298.257223560493],TOWGS84[0,0,0,0,0,0,0]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]







___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Custom Projection

[Birgit Laggner]

Hi Sean,

I picked the appropriate sql query from http://spatialreference.org/. 
It's just an example - you have to adjust it with your own proj4text and 
srid numbers and everything. But if you did so, you could project your 
geometries to this new projection (either with st_setsrid() or with 
st_transform(), whatever is appropriate to your data) and if you then 
export your shapefile with pgsql2shp, I would expect it to write a prj 
file with your custom projection.


This is the example insert statement, I mentioned above:

INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 94326, 'epsg', 4326, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs ', 'GEOGCS["WGS 
84",DATUM["WGS_1984",SPHEROID["WGS 
84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]');

Hope that helps,

Birgit.


Am 05.01.2012 21:44, schrieb Sean Christopher Conway:
Hello, I would like to create a custom projection which is essentially 
a variation on the geographic coordinate system EPSG 4326, i have 
created one and the srtext is "GEOGCS["Geographic Coordinate 
System",DATUM["WGS84",SPHEROID["WGS84",6378137,298.257223560493]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]]""



and the proj4text is this:


"+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 
+y_0=0 +units=m +k=1.0 +nadgrids=@null +no_defs"


My goal is to output a shapefile from postgis with a Geographic 
coordinate system and the datum as : WGS84

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] parsing postgis answers

[Birgit Laggner]

Hi Michal,

you could use a combination of st_xmin, st_ymin, st_xmax and st_ymax 
instead of st_extent. They will provide you with the same coordinates as 
st_extent but in a numeric data type.


Hope that helps,

Birgit.


Am 05.12.2011 12:26, schrieb Micha? Droz.dz.:


Hi List!

I am fresh Postgis user and have a problem with ST_EXTENT. I am using 
it to get data for map.zoomToExtent in openLayers.


The problem is that zoomToExtens takes parameters as 
zoomToExtent(value,value,value,value)


http://dev.openlayers.org/docs/files/OpenLayers/Map-js.html#OpenLayers.Map.zoomToExtent

from ST_EXTENT a receive WKT like this:

"BOX(2294970.88258299 6588308.80462897,2295532.89622321 6588616.7042758)"

How to parse that answer to get paremeters for OpenLayers in 
appropirate format?


I checked that OpenLayers is able to parse WKT but it is impossible 
with BOX type.


http://dev.openlayers.org/docs/files/OpenLayers/Format/WKT-js.html

Thank for help!

Michal




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Help me.

[Birgit Laggner]

Hi Atul,

I often have tasks like this and in the end wrote a set of functions to 
get the wanted result. (if you are interested, have look at: 
http://trac.osgeo.org/postgis/wiki/UsersWikiExamplesOverlayTables2)


But I don't know if that wouldn't be to complicated in your use case... 
You would have to test yourself which way would be more suitable for you.


But if you only want to display three different results together in a 
map, why can't you display them as three separate layers (which may be 
transparent, so you would see the borders of them all)?


Regards,

Birgit.


Am 24.11.2011 11:47, schrieb Atul Kumar:


Hi Birgit,

I want all boundaries of the geometries resulting from the three 
queries to be display in one layer. Its better to me.


If it is not possible then will go with dissolving all inner boundaries .

Please suggest.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 4:12 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hi Atul,

with the st_union function, you aggregate all geometries dissolving 
all inner boundaries. Is this what you want, or do you want all 
boundaries of the geometries resulting from the three queries to be 
displayed in one layer??


Regards,

Birgit.


Am 24.11.2011 10:36, schrieb Atul Kumar:

Hi Birgit,

I also don't know whether st_union is using spatial_index because new 
to postgis.


I am having GUI which shows the data over map.

Suppose user will make query on table1 and queried data display on 
 map. Queried data is geometry type data.


Again user will make query on table2 and queried data display on map. 
Queried data is also geometry type data


Again user will make query on table3 and queried data display on map. 
Queried data is also geometry type data


At the end I want to union above three queried data and display on map 
using resultant queried union data.


Please suggest me is there other way to union geometry data.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
<mailto:postgis-users-boun...@postgis.refractions.net> 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 2:42 PM
*To:* postgis-users@postgis.refractions.net 
<mailto:postgis-users@postgis.refractions.net>

*Subject:* Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are 
mistaken that union in PostGIS means the same as in ArcGIS??), then I 
would split the query again to first do the union of the first two 
tables, while immediately dumping the resulting multipolygon into the 
consisting single polygons, and next, I would go for the union with 
the third table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer 
would be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries 
will still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:

Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
<mailto:postgis-users-boun...@postgis.refractions.net> 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net 
<mailto:postgis-users@postgis.refractions.net>

*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(

Re: [postgis-users] Help me.

[Birgit Laggner]

Hi Atul,

with the st_union function, you aggregate all geometries dissolving all 
inner boundaries. Is this what you want, or do you want all boundaries 
of the geometries resulting from the three queries to be displayed in 
one layer??


Regards,

Birgit.


Am 24.11.2011 10:36, schrieb Atul Kumar:


Hi Birgit,

I also don't know whether st_union is using spatial_index because new 
to postgis.


I am having GUI which shows the data over map.

Suppose user will make query on table1 and queried data display on 
 map. Queried data is geometry type data.


Again user will make query on table2 and queried data display on map. 
Queried data is also geometry type data


Again user will make query on table3 and queried data display on map. 
Queried data is also geometry type data


At the end I want to union above three queried data and display on map 
using resultant queried union data.


Please suggest me is there other way to union geometry data.

Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Thursday, November 24, 2011 2:42 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are 
mistaken that union in PostGIS means the same as in ArcGIS??), then I 
would split the query again to first do the union of the first two 
tables, while immediately dumping the resulting multipolygon into the 
consisting single polygons, and next, I would go for the union with 
the third table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer 
would be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries 
will still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:

Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
<mailto:postgis-users-boun...@postgis.refractions.net> 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net 
<mailto:postgis-users@postgis.refractions.net>

*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(geometry);


--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 
on t1.geometry && t2.geometry where 
st_intersects(t1.geometry,t2.geometry);


--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);


Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:

Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.


My Query is :

select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


I am having three table its having geometry data. I want to intersect 
operation on those data.


Please suggest, Is there any optimal way to get the 

Re: [postgis-users] Help me.

[Birgit Laggner]

Hi Atul,

are you really sure, you want to union all geometries of the three 
tables into one big multipolygon? It seems to me that this might be a 
little bit too complex and big...


But, if you really want to union all these geometries (and I am not 
sure, if this would really be your plan - because maybe you are mistaken 
that union in PostGIS means the same as in ArcGIS??), then I would split 
the query again to first do the union of the first two tables, while 
immediately dumping the resulting multipolygon into the consisting 
single polygons, and next, I would go for the union with the third 
table. The queries could look like this:


select (st_dump(st_union(t1.geometry, t2.geometry))).geom as geometry 
into new_table from TEMP_OUTPUTTREE_2 t1, TEMP_OUTPUTTREE2_4 t2;


select (st_dump(st_union(nt.geometry, t3.geometry))).geom as geometry 
from new_table nt, TEMP_OUTPUTTREE_5 t3;


I don't know if st_union is using the spatial index. If the answer would 
be yes, it might be useful to create one on the new_table. But, 
depending on the size of your tables, I am afraid, that the queries will 
still be much slower than your intersection-queries.


Good luck and regards,

Birgit.



Am 24.11.2011 07:37, schrieb Atul Kumar:


Hi Birgit,

Now its taking less time as compare to previous one.

One more help.

Is there any way to optimize this query because its also taking long 
time to execute.


select st_union (st_union (t1.geometry, t2.geometry), t3.geometry) 
geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


Thanks

Atul

*From:*postgis-users-boun...@postgis.refractions.net 
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of 
*Birgit Laggner

*Sent:* Wednesday, November 23, 2011 6:07 PM
*To:* postgis-users@postgis.refractions.net
*Subject:* Re: [postgis-users] Help me.

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 
using gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 
using gist(geometry);


--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 
on t1.geometry && t2.geometry where 
st_intersects(t1.geometry,t2.geometry);


--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);


Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:

Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.


My Query is :

select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


I am having three table its having geometry data. I want to intersect 
operation on those data.


Please suggest, Is there any optimal way to get the intersection with 
less execution time?


Thanks

Atul Kumar

DISCLAIMER == This e-mail may contain privileged and 
confidential information which is the property of Persistent Systems 
Ltd. It is intended only for the use of the individual or entity to 
which it is addressed. If you are not the intended recipient, you are 
not authorized to read, retain, copy, print, distribute or use this 
message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent 
Systems Ltd. does not accept any liability for virus infected mails.





___
postgis-users mailing list
postgis-users@postgis.refractions.net  
<mailto:postgis-users@postgis.refractions.net>
http://postgis.refractions.net/mailman/listinfo/postgis-users

DISCLAIMER == This e-mail may contain privileged and 
confidential information which is the property of Persistent Systems 
Ltd. It is intended only for the use of the individual or entity to 
which it is addressed. If you are not the intended recipient, you are 
not authorized to read, retain, copy, print, distribute or use this 
message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent 
Systems Ltd. does not accept any liabili

Re: [postgis-users] Help me.

[Birgit Laggner]

Hallo Atul,

perhaps, I would split the query in two queries, because otherwise I 
think it might be difficult to use a spatial index on the second 
intersection. The queries could be like this:


--create a spatial index on each input table:
create index temp_outputtree_2_gist_geometry on temp_outputtree_2 using 
gist(geometry);
create index temp_outputtree2_4_gist_geometry on temp_outputtree2_4 
using gist(geometry);
create index temp_outputtree_5_gist_geometry on temp_outputtree_5 using 
gist(geometry);


--intersection of the first 2 tables using the spatial index, writing 
the result of it into a new table:
select st_intersection(t1.geometry,t2.geometry) as geometry into 
new_table from TEMP_OUTPUTTREE_2 t1 inner join TEMP_OUTPUTTREE2_4 t2 on 
t1.geometry && t2.geometry where st_intersects(t1.geometry,t2.geometry);


--create a spatial index on the new geometries of the first intersection:
create index new_table_gist_geometry on new_table using gist(geometry);

--intersection with the third table:
select st_intersection(nt.geometry,t3.geometry) as geometry from 
new_table nt inner join TEMP_OUTPUTTREE_5 t3 on nt.geometry && 
t3.geometry where st_intersects(nt.geometry,t3.geometry);


Hope that helps,

Birgit.

Am 23.11.2011 13:16, schrieb Atul Kumar:


Hi All,

I am trying  to intersection multiple sets of  geographical data using 
ST_intersection function. But query execution time is long.


My Query is :

select st_intersection (st_intersection (t1.geometry, t2.geometry), 
t3.geometry) geometry from TEMP_OUTPUTTREE_2 t1,TEMP_OUTPUTTREE2_4 t2, 
TEMP_OUTPUTTREE_5 t3


I am having three table its having geometry data. I want to intersect 
operation on those data.


Please suggest, Is there any optimal way to get the intersection with 
less execution time?


Thanks

Atul Kumar

DISCLAIMER == This e-mail may contain privileged and 
confidential information which is the property of Persistent Systems 
Ltd. It is intended only for the use of the individual or entity to 
which it is addressed. If you are not the intended recipient, you are 
not authorized to read, retain, copy, print, distribute or use this 
message. If you have received this communication in error, please 
notify the sender and delete all copies of this message. Persistent 
Systems Ltd. does not accept any liability for virus infected mails.




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] polygon Extraction problem

[Birgit Laggner]

Hi Bruce,

if you use ST_Dump like this, the resulting data type will be an array 
consisting of geometrie and path (internal number of the polygon). In 
order to just get the geometries, you would have to use this kind of query:


select (st_dump(shape)).geom as the_geom from weather_poly;

If you're writing this in a new table you will be able to access it with 
QGIS.


Regards,

Birgit.


Am 27.10.2011 04:06, schrieb Clay, Bruce:


I have a table of points extracted from a raster file. I perform the 
following SQL on the table


CREATE TABLE weather_poly as

SELECT(pixel_val, ST_Transform(ST_Union(ST_Buffer(shape, 0.1)),4326)) FROM

weather_points GROUP BY pixel_val;

The resulting polygon is 13 multi-polygons one for each pixel value 
instead of one polygon for every cluster of points.


I can run the following SQL select ST_Dump(shape) from weather_poly a 
to break the polygons so they appear as separate polygons in 
Postgresql data view.


When I try to view them in QGis the table does not show on the list of 
available PostGis layers. The properties of the table in Postgresql 
show the geometry to be ST_Dump not polygon.


Is there a way to split the multi-polygon into individual polygons or 
to build the original polygons separatly so they will show in QGis and 
be accessable as such by GDAL based applications?


Bruce

This message and any enclosures are intended only for the addressee.  Please
notify the sender by email if you are not the intended recipient.  If you are
not the intended recipient, you may not use, copy, disclose, or distribute this
message or its contents or enclosures to any other person and any such actions
may be unlawful.  Ball reserves the right to monitor and review all messages
and enclosures sent to or from this email address.


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Insersection problem

[Birgit Laggner]

Hallo Ge,

this intersection error happens very often to me, too. Mostly, this is 
really due to invalid polygons. My experience is, that the validity of 
some polygons seems to depend on precision issues. If I am testing these 
geometries with st_isvalid, everything seems to be fine, but if I do a 
conversion into wkt format and back on the same geometries, the result 
is not valid anymore. So, my first step in your case would be to test 
validity of the converted geometries and repair the invalid ones.


If the problem of the intersection error still occurs, my next proposal 
would be to execute the intersection within a function with a loop and 
an exception handler. And in the exception handler I would experiment 
with small buffers and small polygon corrections with st_snaptogrid().


If you need more details or an example, just ask...

Birgit.


Am 04.10.2011 09:58, schrieb G. van Es:

Hi Andrea,

I'm still working on this issue. To my knowledge there are no 
Collections. The GeometryType on all records in both tables is 
POLYGON. So I expect the LINESTRING error to be an internal issue 
where I'm unable to detect or prevent this from happening.


I you or anybody else has some idea's please let me know.

thanks,
Ge


*From:* Andrea Peri 
*To:* PostGIS Users Discussion 
*Sent:* Thursday, September 22, 2011 12:02 PM
*Subject:* Re: [postgis-users] ST_Insersection problem

>Hello Group,
>We are using "POSTGIS="1.5.1" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" 
LIBXML="2.7.6" USE_STATS" and having a problem with ST_Intersection().

>Given the following query
>select count(st_intersection(tbl_a.the_geom,tbl_b.the_geom)) from tbl_a, tbl_b;
>will result in this error message
>NOTICE:  TopologyException: found non-noded intersection between LINESTRING 
(62723.7 426635, 62722.5 426634) and LINESTRING (62723.7 426635, 62726.2 426632) 
at 62723.7 426635

>ERROR: GEOS Intersection() threw an error!
>SQL status:XX000
>
>After the NOTICE message we expect the query to continue but it doesn't.  Both 
tables have all records st_isvalid='t'.
>

>Does anyone know a solution or workaround for this problem?
>
>Thanks,

Hi,
I give my 2ct. :)
The problemyou report is not really a problem of postgis, but instead is a 
problem of the finite arithmetic use by the pcs and of the methematical 
algorithm used .


I have every time the error you report.
Please notice I run about 10-20 million records of geometry and some geometry 
has also 1 million vertex :)

Is a nightmare.
But this is the beautiful and the hell of the arithmetic finite .


To resolve this you must detect at every step what happened and filter they using 
specific "where" clause or CASE operators.

Is pretty easy,
you will see often it return a Collection, and you get only the lines or the 
polys from that, again

you can filter out the empty geometry.
After this long path you will have a good procedure to clean all the problem of 
a real intersection on a arithmetic finite machine.

Regards,
Andrea Peri.



--
-
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-


___
postgis-users mailing list
postgis-users@postgis.refractions.net 


http://postgis.refractions.net/mailman/listinfo/postgis-users




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] terminated server connection during st_isvalid()

[Birgit Laggner]

Hi --strk;

yes, I did receive the notifications. I also followed the link to the 
GEOS bug ticket and did see you already solved the problem!


Thanks for your immediate reaction!

Regards,

Birgit.

Am 09.06.2011 12:37, schrieb Sandro Santilli:

On Wed, Jun 8, 2011 at 3:24 PM, Birgit Laggner
  wrote:


I did file a ticket and hopefully did right... Maybe someone more capable
could check my specifications (I don't want to decide about priority or
milestone...).

Thank you.
The bug is in GEOS (and also JTS).
I'm on it.
You should have received notifications by the bug tracker (did you?)

--strk;

Free GIS&  Flash consultant/developer
http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] terminated server connection during st_isvalid()

[Birgit Laggner]

Hi,

I did file a ticket and hopefully did right... Maybe someone more 
capable could check my specifications (I don't want to decide about 
priority or milestone...).


Thanks and regards,

Birgit.


Am 08.06.2011 14:09, schrieb Sandro Santilli:

On Tue, Jun 7, 2011 at 3:32 PM, Mike Toews  wrote:

On 8 June 2011 01:26, Birgit Laggner  wrote:

But I am still curious why this crashes the server connection...

It looks like a bug. With a recent SVN version of PostGIS on PG 8.4
here is the moment before the crash:

NOTICE:  [lwgeom.c:lwgeom_release:571] releasing type Polygon
NOTICE:  [lwgeom.c:lwgeom_release:576] lwgeom_release: releasing bbox.
0xa1f6b18** Error **

Could you please file a ticket attaching the dataset and all informations ?
It'll be very helpful.
Thanks.

--strk;

Free GIS&  Flash consultant/developer
http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] terminated server connection during st_isvalid()

[Birgit Laggner]

Hi Nicolas,

thanks for your answer! Just presuming the polygon is invalid and 
buffering with zero did work for me, too :-) Thanks!


But I am still curious why this crashes the server connection...

Regards,

Birgit.



Am 07.06.2011 15:01, schrieb Nicolas Ribot:

Looking at it more closely, it is definitively invalid, with self
intersections (see picture), though don't know why it crashed
Postgresql.

Nicolas


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Birgit Laggner]

  
  
Hi Shreyes,

if I understand your question right, it's not much different from
what you asked before. You would select the intersecting geometries
of both tables by asking for
st_intersects(a.the_geom,b.the_geom)=true, then intersect those
geometries with st_intersection() and then calculate the area of the
intersection with st_area(). For the identification of the origin of
the resulting geometries, you select the id columns of the tables
plots and parcels, too - so, you would see that plot S01BAT009 does
have intersecting geometries with parcels P1, P2 and P3 (in your
example). Here an example for such a query:

select
a.plot_id,
b.parcel_id,
st_area(st_intersection(a.the_geom, b.the_geom)) as the_geom
from
plots a
inner join
parcels b
on a.the_geom && b.the_geom
where st_intersects(a.the_geom, b.the_geom);

I don't know exactly what you mean by redundant tuples - perhaps it
would help, if you would post your query and an example of its
result here too...

Regards,

Birgit.


Am 05.05.2011 14:35, schrieb shreyes shiv:
Dear all,
  
  
  I have two shape files plots and parcels.
  
  
  the attributes of the "plots" shape file are
  plot_id
  plot_area
  owner_nm
  the_geom
  
  
  the attributes of the "parcels" shape file are
  parcel_id
  parcel_area
  conversion
  the_geom
  
  
  when i do the ST_Intersects operation on the shape files it
returns a table that contains redundant tuples.
  what i want is the area of the newly formed polygons which
are formed after the intersection as an extra column.
  
  
  This image explains my problem.
  
  
  
  
  
The Plot S01BAT009 lying in Parcels P1, P2 and P3, and also
  occupying the area A1, A2 and A3 in the respective parcels.
  The white lines are the parcel boundaries. The black lines are
  the plot boundaries.
I want the areas A1, A2 and A3.


please help.




  
  
  -- 
  shreyes shiv
  email: shivshre...@gmail.com
  phone: 9557975780
  IIRS(Indian Institute of Remote Sensing)
  No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India

  

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Birgit Laggner]

Hi Sandro,

Am 04.05.2011 11:49, schrieb Sandro Santilli:

On Wed, May 04, 2011 at 09:30:01AM +0200, Birgit Laggner wrote:


insert into new_table
select a.id, b.id, st_intersect(a.the_geom, b.the_geom)
from
table_a a
inner join
table_b b
on a.the_geom&&  b.the_geom
where st_intersects(a.the_geom, b.the_geom);


- st_intersect(a.the_geom, b.the_geom)
+ st_intersection(a.the_geom, b.the_geom)

yes!! this was a typo :-)

... and I'd add ( a.id>  b.id ) in the WHERE condition.

But why this, if table_a and table_b are different tables??

Regards,

Birgit.

--strk;

   ()   Free GIS&  Flash consultant/developer
   /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ST_Intersects

[Birgit Laggner]

Hi Shreyes,

you have to create a new table and then insert the result of the 
st_intersect in the new table, e.g. like this:


insert into new_table
select a.id, b.id, st_intersect(a.the_geom, b.the_geom)
from
table_a a
inner join
table_b b
on a.the_geom && b.the_geom
where st_intersects(a.the_geom, b.the_geom);

Hope that helps,

Birgit.


Am 04.05.2011 07:14, schrieb shreyes shiv:

Dear all,

I want to use the ST_Intersect on two tables. I want the resultant of 
the ST_Intersect to form a new table. How do i do that.


Please help



--
shreyes shiv
email: shivshre...@gmail.com 
phone: 9557975780
IIRS(Indian Institute of Remote Sensing)
No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] spatial query (ST_Intersects)

[Birgit Laggner]

Hi Shreyes,

to select only the first row of a group of rows ordered by the values of 
a certain column, I would use DISTINCT ON. For your example table this 
would look like this:


select distinct on (plot_id) plot_id, area from intersects_result order 
by plot_id, area desc;


Hope that helps,

Birgit.



Am 03.05.2011 14:11, schrieb shreyes shiv:

Dear all,

I have loaded two shape files into postgis database.
I am using the ST_Intersects(geom of shape file1, geom of shape file2) 
command to see the features that interesects.

Now there are redundant entries created in the table.
I want to pick the unique tuple where the attribute "area" has the 
maximum value.
for ex. the following is a outcome of doing the intersects operation 
on the two tables.


plot_id  area

S01BAT009  23
S01BAT009  20
S01BAT009  19
S01BAA001  10
S01BAA001  12
S01BAA001  30

I want to select

plot_id   area
---
S01BAT00923
S01BAA001   30

from the above table.

How do i write the query please help.

thank you

--
shreyes shiv
email: shivshre...@gmail.com 
phone: 9557975780
IIRS(Indian Institute of Remote Sensing)
No. 4, Kalidas Road, Dehradun-248001, Uttarakhand, India


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] converting to postgis

[Birgit Laggner]

Hi Farouk,

I always use a workaround in order to convert a shp file with special 
characters in its attribute table into UTF-8 encoding: I open the shp 
file's dbf in some calculation software (e.g. OpenOffice) and save it as 
an csv with encoding UTF-8. Then I open the csv and save it again as a 
dbf under a new name. Then, I exchange the old dbf by the new one (don't 
forget to change the name to, so it does match the shp file name) and 
the shp file gets imported into the postgis database without problems.


There are hopefully more elegant solutions, but for shp-import of only a 
few files, it's ok...


Regards,

Birgit.

Am 29.04.2011 10:32, schrieb farouk azzouhri:

Hello,
I converted an Access database (contains two fields X and Y) in a .shp 
file it's ok, but when I wanted to convert the .shp into a postgis 
database ( using the shp2pgsql-gui.exe ) it gives me the following 
message:


Unable to convert field name to UTF-8 (iconv reports "Illegal byte 
sequence"). Current encoding is "UTF-8". Try "latin1 " (Western 
European), One Of The gold values ​​at 
http://www.postgresql.org/docs/current/static/multibyte.html Described.

Shapefile import failed.

how I'm going to do ??


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Calculating variance of a set of points

[Birgit Laggner]

Hi Aren, hi Brent,

I would only add to Brent's suggestion, that you could use st_centroid 
of the collected multipoint instead of calculating average x and y 
points for each reference mark - perhaps like this:


select l.ref_mark_id,
  avg(ST_Distance(l.location,a.avg_location))
from loc_table l
inner join
(select ref_mark_id,
   st_centroid(st_collect(location)) as avg_location
 from loc_table
 group by ref_mark_id) a
on l.ref_mark_id=a.ref_mark_id
group by l.ref_mark_id;

Regards,

Birgit.



Am 25.04.2011 03:22, schrieb pcr...@pcreso.com:

Hi Aren,

If I understand the question, then off the top of my head, untested & 
without getting into calculating spheroidal distances instead of 
cartesian ones :-) ...


given a table loc_table with columns:
ref_mark_id
location (point geometry)

something like:

select ref_mark_id,
  avg(ST_Distance(l.location,a.avg_location))
from loc_table l,
(select ref_mark_id,
   setsrid(makepoint(avg(x(location)), 
avg(y(location))),4326) as avg_location

 group by ref_mark_id as foo) a
where l.ref_mark_id - a.ref_mark_id
group by ref_mark_id;

Should work. ie: generate a virtual table as a query which provides 
the average X/Y point for each marker, then join this to the original 
table by marker to average the distances between each point & the 
average point, grouped by marker.


HTH

Brent Wood


--- On *Mon, 4/25/11, Aren Cambre //* wrote:


From: Aren Cambre 
Subject: [postgis-users] Calculating variance of a set of points
To: "PostGIS Users Discussion" 
Date: Monday, April 25, 2011, 12:42 PM

I have a table with events. Each event happened at a listed
reference marker on a highway, and it also has latitude and
longitude as recorded by an observer of the event.

There are many events at each reference marker.

I want to check the precision of the latitude and longitude for
events recorded at each reference marker. Roughly, I would collect
all latitude/longitude points at each reference marker, then I
would want to see the average distance between each point and a
centroid of all the points.

How would I do that with PostGIS?

I understand how to convert latitude/longitude to a geometry type,
but I am not clear how to do the rest without iterating through
each point individually.

Aren

-Inline Attachment Follows-

___
postgis-users mailing list
postgis-users@postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Need help with a query to assign attributes to lines the intersect multiple polygons

[Birgit Laggner]

Hi Steve,

I thought about your problem and I would solve it with distinct on  - 
like this:


update line set poly_att=sel.poly_att from (select distinct on 
(a.line_id) a.line_id, b.poly_att from line a inner join polygons b on 
a.the_geom && b.the_geom where st_intersects(a.the_geom,b.the_geom) 
order by a.line_id, st_length(st_intersection(a.the_geom, b.the_geom)) 
desc) sel where line.line_id=sel.line_id;


Hope that helps,

Birgit.


Am 25.04.2011 16:24, schrieb Stephen Woodbridge:

Hi all,

I have a table a line and a table of polygons.
I would like to come up with a query that allow me to link the lines 
to the polygon where most of the length of the line falls into that 
polygon.


The problem is that the lines may overlap or touch more than one 
polygon. So I can do this in a stored procedure where I iterate 
through the lines and intersect them with the overlapping polygons and 
sort them based on the length of the intersection fragments and limit 
1 on the results.


It just seems like this should be able to be done using join and 
sub-query of some kind, but I can't quite figure it out. Ultimately, I 
want to do an UPDATE line set attr1=b.someattr ...


Thoughts,
  -Steve
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Clip and select outside an polygon

[Birgit Laggner]

Hi Júlio,

this would be done with ST_Difference(lines_geom, polygon_geom).

http://postgis.refractions.net/documentation/manual-1.5/ST_Difference.html

Hope that helps,

Birgit.


Am 19.04.2011 16:49, schrieb Júlio Almeida:

Hello,


I need to clip/trim from a geometry the geometries that 
ST_Intersection(r.the_geom, m.the_geom) and select the geometries that 
area outside of an ST_Intersection(r.the_geom, m.the_geom).

I have attached an image to illustrate.

How can it be done?
Thanks,

julio



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Res: [Annouce] GISVM Base

[Birgit Laggner]

Hi Ricardo,

did you already notice the OSGeo Live project? I think they are working 
on something similar (Virtual machines or boot DVDs with preinstalled 
open-source GIS applications). I did not try it yet, but perhaps it 
would be worth a look for you:


http://live.osgeo.org/en/index.html

Regards,

Birgit.


Am 13.04.2011 08:56, schrieb Ricardo Pinho:

Sorry, just forgot to mention a very important aspect!

GISVM BASE comes already installed with:
- PostgreSQL + PostGIS + PG Admin
- Mapserver + PHP + PHP Mapscript
- Apache + Tomcat + Java JDK

Cheers,
Ricardo


*De:* Ricardo Pinho 
*Para:* PostGIS Users Discussion 
*Enviadas:* Quarta-feira, 13 de Abril de 2011 7:52:07
*Assunto:* Res: [postgis-users] [Annouce] GISVM Base

Thank you Jorge.
We really appreciate your opinion and suggestions!
Let me know if need help on that conversion.
Cheers,
Ricardo


*De:* Jorge Arévalo 
*Para:* PostGIS Users Discussion 
*Cc:* Ricardo Pinho 
*Enviadas:* Quinta-feira, 7 de Abril de 2011 8:08:23
*Assunto:* Re: [postgis-users] [Annouce] GISVM Base

On Thu, Apr 7, 2011 at 2:44 AM, Ricardo Pinho > wrote:

> Dear all,
> We are proud to announce the new “GISVM BASE”.
> Available now for FREE DOWNLOAD at: http://gisvm.com
>
> GISVM ("GIS Virtual Machine") is a Ubuntu Linux distribution for GIS 
users,
> packaged as a virtual machine, a completely isolated computer that 
can be

> run in a window inside your current operating system.
>
> This new BASE edition, based on a minimum Ubuntu 10.10 installation,
> provides the user with simple Double Click install scripts for an 
unlimited

> possible number of open-source GIS packages.This way, users are FREE to
> build their own GISVM by installing the desired GIS packages.
>
> By the time of this announcement these are the available install 
scripts:

> - Quantum GIS / Grass
> - uDIG
> - gvSIG
> - OpenJump
> - Kosmo
> - R
> - GeoServer
> - GeoMajas
>
> We plan to keep adding more install scripts, depending on the users 
feedback

> and request.
>
> PLEASE REGISTER ON THE GISVM USER MAILLING LIST and help us improve 
this new

> version: https://lists.sourceforge.net/lists/listinfo/gisvm-users
>
> We would also appreciate it if you SPREAD THE WORD. Put it in your blog,
> forum, facebook, twitter, webpage, and so on.
>
> Thank you,
> Ricardo Pinho
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net 


> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>

Hi Ricardo,

That sounds really great! I was working in a VM like that, executed in
an Amazon EC2 microinstance (free tier). Now, I think I can try a kind
of "VMWare to AMI" process, using your GISVM. Maybe adding PostGIS
Raster too.

That could help:
http://thewebfellas.com/blog/2008/9/1/creating-an-new-ec2-ami-from-within-vmware-or-from-vmdk-files

Many thanks! I'll publish your work in my blog, and I'll tweet it.

--
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arev...@deimos-space.com 
http://es.linkedin.com/in/jorgearevalo80
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com
http://geohash.org/ezjqgrgzz0g



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] MULTILINESTRING to MULTIPOLYGON

[Birgit Laggner]

Hi Javier,

you could add the missing end point (and then try again with 
ST_BuildArea() etc.). Here a more or less suiting example of the PostGIS 
manual for ST_AddPoint():


UPDATE sometable
SET the_geom = ST_AddPoint(the_geom, ST_StartPoint(the_geom))
FROM sometable
WHERE ST_IsClosed(the_geom) = false;


With a MultiLineString, this might be a bit more complicated because you 
have to find out first, is your multilinestring resulting in a polygon 
or a multipolygon? If all linestrings form one polygon, you have to 
select the start point of the first linestring as end point of the last 
linestring. If the result is a multipolygon, you have to find out which 
of the rings is not closed, first. As I see from your first e-mail, you 
are expecting the geometry to become a multipolygon, so (assuming every 
linestring is representing a polygon within the multipolygon, otherwise 
it might be a solution to union the linestrings first) maybe the sql 
code could look like this:


UPDATE sometable a
SET the_geom = sel.geom FROM
(SELECT b1.gid, ST_Collect(b1.geom) AS geom --put together all linestrings to 
one multilinestring again
FROM
(SELECT b.gid, ST_AddPoint(b.geom, ST_StartPoint(b.geom)) --add an end point on 
the position of the start point for each non-closed linestring
FROM
(SELECT gid, (st_dump(the_geom)).geom AS geom --separate the linestrings and 
select the not closed ones
FROM sometable
WHERE ST_IsClosed(the_geom)=false) b
WHERE ST_IsClosed(b.geom) = false
UNION ALL
SELECT b.gid, b.geom
FROM
(SELECT gid, (st_dump(the_geom)).geom AS geom --separate the linestrings and 
select the closed ones
FROM sometable
WHERE ST_IsClosed(the_geom)=false) b
WHERE ST_IsClosed(b.geom) = true) b1
GROUP BY b1.gid) sel
WHERE ST_IsClosed(a.the_geom)=false AND a.gid=sel.gid;

I did not test the code, but I hope you get the idea...

Regards,

Birgit.


On 28.03.2011 18:47, Javier Perez wrote:

Hi,

I tried |*ST_BuildArea*(|geometry A|)|;  and 
|*ST_BdMPolyFromText*(|text WKT, integer srid|)|;


but the output is empty. I think it's becouse this functions may need 
the MULTILINESTRING to be closed and it's not.


Any suggestion?

2011/3/25 Javier Perez >


Hi Marc-Andre!

I think this usefull.

Thanks a lot!

Regards

2011/3/25 Morin, Marc-André mailto:marc-andre.mo...@dfo-mpo.gc.ca>>

*Hi,*
**
*This is the recipe I found in the past to get it done:*
**

http://postgis.refractions.net/pipermail/postgis-users/2008-May/019901.html
It worked well for me.
Regards,
Marc-André

*De :* postgis-users-boun...@postgis.refractions.net

[mailto:postgis-users-boun...@postgis.refractions.net
] *De la
part de* Javier Perez
*Envoyé :* March 25, 2011 7:35 AM
*À :* postgis-users@postgis.refractions.net

*Objet :* [postgis-users] MULTILINESTRING to MULTIPOLYGON

Hi!,

In my app I get a MULTILINESTRING and want to insert it as
MULTIPOLYGON. I think there's an easy way but I did't found,
can anyone bring some light?


Thanks in advance


___
postgis-users mailing list

postgis-users@postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users




___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] dissolve attributes in a table (no geometry)

[Birgit Laggner]

 Hi Júlio,

you could use DISTINCT ON:

select distinct on (point_id) point_id, line_id, distance from 
worktemp.distances2streets order by point_id, distance;


This selects for every point_id the first row of every point_id group. 
Since you have ordered by distance too, the shortest distance is always 
on top of the list and will therefore be selected.


Regards,

Birgit.

On 16.03.2011 12:54, Júlio Almeida wrote:


Hello,

I have a table with point_id, line_id, and distance (from point to line).
I need to create a new table with the record for the shortest distance for
each point.
I have tried:

select point_id,
line_id,
min(distance)
from worktemp.distances2streets group by point_id;

but i get the message:

ERROR:  column "distances2streets.line_id" must appear in the GROUP BY
clause or be used in an aggregate function
LINE 2: line_id,
   ^

** Erro **

ERROR: column "distances2streets.line_id" must appear in the GROUP BY 
clause

or be used in an aggregate function
Estado de SQL:42803
Carácter:18


Any suggestion?

Thanks


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] bug in st_difference() ?

[Birgit Laggner]

 Hi Ralf,

I have to correct me: it's not vertexes 4 and 8, I am worried about, 
it's vertexes 3 (3597943 5323120) and 9 (3606024 5322587) and they are 
both vertexes in both input polygons.
But, I thought about your explanation again and I think, I understand 
now, what you are talking about: These 2 float points of the second 
input polygon are probably located slightly within the first input 
polygon (although, we are talking about nanometers!!!). Therefore 
PostGIS takes them as vertexes for the result polygon and takes the next 
vertexes too, because at these points the borders match again. Well, I 
got the coordinates of the 2 float points from an intersection with 
another polygon - how am I supposed to get the correct coordinates 
causing no artefacts during ST_Difference() (Just mentioning: I am using 
PostGIS 1.5, so ST_Snap() is no option)? Wouldn't it be easier to make 
ST_Difference() not more precise than ST_Intersection()? What use has 
such a high coordinate precision if the functions (or im- and export 
modules?) can't handle it.


Thank you anyway (allthough it took a while for me to understand ;-) ),

Birgit.


On 10.03.2011 09:03, Birgit Laggner wrote:

 Hi Ralf,

sorry, I don't understand. I don't want these two points in the result 
polygon of the difference, because they are located in a region that 
should be erased by the difference. I don't need them to become more 
precise. Or am I misunderstanding what you are saying?


Regards,

Birgit.

On 09.03.2011 16:04, Ralf Suhr wrote:

Hi Birgit,

this is only a topoligie Problem. Your secound polygon is computed 
outside
PostGIS and numeric precision are lost by import. In this case 
ST_Difference()

and other PostGIS function can have results with very small errors.

You can clean the second polygon by move all points to the borders 
from first.

3597691.18360053 5323329.04452537 becomes
3597691.183600531425327 5323329.044525371864438 and so on.

Gr
Ralf

Am Mittwoch 09 März 2011, 15:05:43 schrieb Birgit Laggner:

   Dear list,

when I am using ST_Difference(), I often have the problem, that the
resulting geometries contain vertexes lying outside of the supposed
geometry extent. My impression is that the function tries to preserve
vertexes of the input geometries and does not check properly, whether
the point lies inside the extent of the new geometry or not.

Here an example:

select st_astext(st_difference(a_geom, b_geom)) from
st_geomfromtext('POLYGON((3580359 5328360,3588529 5330935,3597943
5323120,3606024 5322587,3612951 5315217,3608155 5306691,3603804
5305093,3596166 5306159,3591105 5310244,3584799 5310688,3576540
5317437,3580359 5328360))',31467) as a_geom,
st_geomfromtext('POLYGON((3597691.18360053 5323329.04452537,3597943
5323120,3606024 5322587,3611561.81746298 5316695.02444028,3611480
5316582,3605931 5322425,3597573 5323069,3597691.18360053
5323329.04452537))',31467) as b_geom;

results in:
"POLYGON((3580359 5328360,3588529 5330935,3597943
5323120,3597691.18360053 5323329.04452537,3597573 5323069,3605931
5322425,3611480 5316582,3611561.81746298 5316695.02444028,3606024
5322587,3612951 5315217,3608155 5306691,3603804 5305093,3596166
5306159,3591105 5310244,3584799 5310688,3576540 5317437,3580359 
5328360))"


Vertexes 4 (3597691.18360053 5323329.04452537) and 8 (3611561.81746298
5316695.02444028) are out of the supposed geometry extent.

I am using PostGIS 1.5.2 with GEOS 3.2.2 on a 8.4.5 PostgreSQL-DB on
64-bit SUSE Linux.

Maybe, there is a simple explanation, why this behavior might be wanted
- if not, I would like to complain this as a bug (or at least 
suggest an

improvement of the function in future versions)...

Regards,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] bug in st_difference() ?

[Birgit Laggner]

 Hi Ralf,

sorry, I don't understand. I don't want these two points in the result 
polygon of the difference, because they are located in a region that 
should be erased by the difference. I don't need them to become more 
precise. Or am I misunderstanding what you are saying?


Regards,

Birgit.

On 09.03.2011 16:04, Ralf Suhr wrote:

Hi Birgit,

this is only a topoligie Problem. Your secound polygon is computed outside
PostGIS and numeric precision are lost by import. In this case ST_Difference()
and other PostGIS function can have results with very small errors.

You can clean the second polygon by move all points to the borders from first.
3597691.18360053 5323329.04452537 becomes
3597691.183600531425327 5323329.044525371864438 and so on.

Gr
Ralf

Am Mittwoch 09 März 2011, 15:05:43 schrieb Birgit Laggner:

   Dear list,

when I am using ST_Difference(), I often have the problem, that the
resulting geometries contain vertexes lying outside of the supposed
geometry extent. My impression is that the function tries to preserve
vertexes of the input geometries and does not check properly, whether
the point lies inside the extent of the new geometry or not.

Here an example:

select st_astext(st_difference(a_geom, b_geom)) from
st_geomfromtext('POLYGON((3580359 5328360,3588529 5330935,3597943
5323120,3606024 5322587,3612951 5315217,3608155 5306691,3603804
5305093,3596166 5306159,3591105 5310244,3584799 5310688,3576540
5317437,3580359 5328360))',31467) as a_geom,
st_geomfromtext('POLYGON((3597691.18360053 5323329.04452537,3597943
5323120,3606024 5322587,3611561.81746298 5316695.02444028,3611480
5316582,3605931 5322425,3597573 5323069,3597691.18360053
5323329.04452537))',31467) as b_geom;

results in:
"POLYGON((3580359 5328360,3588529 5330935,3597943
5323120,3597691.18360053 5323329.04452537,3597573 5323069,3605931
5322425,3611480 5316582,3611561.81746298 5316695.02444028,3606024
5322587,3612951 5315217,3608155 5306691,3603804 5305093,3596166
5306159,3591105 5310244,3584799 5310688,3576540 5317437,3580359 5328360))"

Vertexes 4 (3597691.18360053 5323329.04452537) and 8 (3611561.81746298
5316695.02444028) are out of the supposed geometry extent.

I am using PostGIS 1.5.2 with GEOS 3.2.2 on a 8.4.5 PostgreSQL-DB on
64-bit SUSE Linux.

Maybe, there is a simple explanation, why this behavior might be wanted
- if not, I would like to complain this as a bug (or at least suggest an
improvement of the function in future versions)...

Regards,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] bug in st_difference() ?

[Birgit Laggner]

 Dear list,

when I am using ST_Difference(), I often have the problem, that the 
resulting geometries contain vertexes lying outside of the supposed 
geometry extent. My impression is that the function tries to preserve 
vertexes of the input geometries and does not check properly, whether 
the point lies inside the extent of the new geometry or not.


Here an example:

select st_astext(st_difference(a_geom, b_geom)) from 
st_geomfromtext('POLYGON((3580359 5328360,3588529 5330935,3597943 
5323120,3606024 5322587,3612951 5315217,3608155 5306691,3603804 
5305093,3596166 5306159,3591105 5310244,3584799 5310688,3576540 
5317437,3580359 5328360))',31467) as a_geom, 
st_geomfromtext('POLYGON((3597691.18360053 5323329.04452537,3597943 
5323120,3606024 5322587,3611561.81746298 5316695.02444028,3611480 
5316582,3605931 5322425,3597573 5323069,3597691.18360053 
5323329.04452537))',31467) as b_geom;


results in:
"POLYGON((3580359 5328360,3588529 5330935,3597943 
5323120,3597691.18360053 5323329.04452537,3597573 5323069,3605931 
5322425,3611480 5316582,3611561.81746298 5316695.02444028,3606024 
5322587,3612951 5315217,3608155 5306691,3603804 5305093,3596166 
5306159,3591105 5310244,3584799 5310688,3576540 5317437,3580359 5328360))"


Vertexes 4 (3597691.18360053 5323329.04452537) and 8 (3611561.81746298 
5316695.02444028) are out of the supposed geometry extent.


I am using PostGIS 1.5.2 with GEOS 3.2.2 on a 8.4.5 PostgreSQL-DB on 
64-bit SUSE Linux.


Maybe, there is a simple explanation, why this behavior might be wanted 
- if not, I would like to complain this as a bug (or at least suggest an 
improvement of the function in future versions)...


Regards,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[Birgit Laggner]

 Thanks, ThomasG!!


On 17.02.2011 11:12, Thomas Gratier wrote:

Hello,

st_snap() function is in the 2.0 future release.

You find informations about this function in the trunk documentation 
http://postgis.refractions.net/documentation/manual-svn/reference.html
and for the particular function you're looking for 
http://postgis.refractions.net/documentation/manual-svn/ST_Snap.html


Regards

ThomasG
GIS specialist

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Retrieve the portion of line external to polygon

[Birgit Laggner]

 Hi strk,

sorry to disturb your discussion, but I am interested in this st_snap() 
function you mention. I did not found it - is this something in PostGIS 2.0?


Regards,

Birgit.


On 16.02.2011 19:18, strk wrote:

On Wed, Feb 16, 2011 at 07:08:03PM +0100, strk wrote:

On Wed, Feb 16, 2011 at 06:47:53PM +0100, Andrea Peri wrote:

Look this simple example

the difference between the line and the same line intersected with the
polygon is equal to the line :)

select 1, ST_Difference(ST_GeomFromText('LINESTRING(10.9 2, 11
8)'),ST_Intersection(ST_GeomFromText('LINESTRING(10.9 2, 11 8)'),
ST_GeomFromText('POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))')))

the trick is that in the intersection point between polygon and line, the
vertex added move the line so the difference fail to
remove the internal portion.

Ok, I think I know what's going on.
The original linestring isn't noded with the polygon boundary.
When computing the intersection, a node is added.
Such a node will NOT fall on the original line anymore, due
to precision constraints.

See this:
  CREATE TABLE a AS SELECT
'LINESTRING(10.9 2, 11 8)'::geometry as g;
  CREATE TABLE b AS SELECT
'POLYGON((12 2, 10 10, 20 21, 21 5, 12 2))'::geometry as g;
  CREATE TABLE c AS SELECT
ST_Intersection(a.g, b.g) FROM a, b;
  SELECT ST_Covers(a.g, ST_StartPoint(c.g)) FROM a,c; -- false
  SELECT ST_Covers(a.g,   ST_EndPoint(c.g)) FROM a,c; -- true

What you could do is _snap_ the original line to the intersection,
or to node the input before proceeding.

For a working example:
select st_covers(a.g, st_startpoint(st_snap(a.g,c.g,1e-10))) from a,c;

--strk;

   ()   Free GIS&  Flash consultant/developer
   /\   http://strk.keybit.net/services.html
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] syntax to pick correct, single result from subquery

[Birgit Laggner]

 Hi Karsten,

try (I hope I made no mistake...):

update ca.r_model_cells a set quad_id =b.quad_id from
(select quad.quad_id, r_model_cells.record_id rid from ca.r_model_cells 
inner join ca.quad

on quad.merc_geom && r_model_cells.merc_geom
where st_within(r_model_cells.merc_geom,quad.merc_geom)) b
where a.record_id=b.rid;

You have to relate the results of the subquery to some row identificator 
in the target table, otherwise it tries to write all results of the 
subquery into every row of the target table...


Regards,

Birgit.

On 16.02.2011 10:03, karsten vennemann wrote:

Ok I am fighting with this SQL task:
I have a polygon layer "quad" and a point location layer 
"r_model_cells" in PostGIS. In each of the quads there will be one or 
more of the pints located.
Now I would like to update a field 'quad_id' in he point layer 
"r_model_cells" with the id of the quad it is located within. Thus I 
can get all point ids and respective quad ids they are via this query
select quad.quad_id qid, r_model_cells.record_id rid from ca.quad, 
ca.r_model_cells

where st_within(r_model_cells.merc_geom,quad.merc_geom);
but how can I use this query to update a column my field quad_id in he 
point layer. I can'get my snytax together :(

I also have this so far
update ca.r_model_cells set quad_id =
(select quad.quad_id from ca.quad, ca.r_model_cells
where
quad.merc_geom && r_model_cells.merc_geom
and intersects(quad.merc_geom,r_model_cells.merc_geom));
But this query will rerieve more then one records in the subquery ...
Thus I get ERROR:
more than one row returned by a subquery used as an expression I want 
to limit to one which correcponds to the point in question

Any ideas what my syntax should be ?
Note: Right now I can do it via two queries below
-
select quad.quad_id qid, r_model_cells.record_id AS rid
into temp_rid
from ca.quad, ca.r_model_cells
where st_within(r_model_cells.merc_geom,quad.merc_geom) ;
and then
update ca.r_model_cells set quad_id =
qid from (select temp_rid.qid qid, temp_rid.rid, 
r_model_cells.record_id from temp_rid, ca.r_model_cells

where temp_rid.rid =r_model_cells.record_id ) as bubu ;
-
Cheers
Karsten


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Declarative SQL query for non-overlapping buffers given dense points?

[Birgit Laggner]

 Hallo Stefan and Pierre,

are you sure this could work? I would expect that you can't find any 
results because every geometry you are testing with NOT ST_Within() is 
part of the ST_Union() in B. Unfortunately, I have no other idea how to 
solve that without a stored procedure...


Regards,

Birgit


On 15.02.2011 07:52, Stefan Keller wrote:

Salut Pierre

Nice hint :->
I also wanted to have at least on labels even in dense areas (picking
one randomly or taking elevation as priority criterion).
I tried some variants like this - and it's really slow although there
are only about 1500 features involved!
I canceled the query after some minutes...

SELECT name, ele, ST_AsText(way)
FROM
   planet_osm_point A,
   (SELECT ST_Union(ST_Buffer(way, 1)) AS geom
FROM planet_osm_point
WHERE tourism='viewpoint') B
WHERE
   NOT ST_Within(ST_Buffer(A.way,1), B.geom)
   AND tourism='viewpoint'
ORDER BY 1

I think its the ST_Union and ST_Within which are to blame:

"Sort  (cost=2607.06..2607.74 rows=270 width=119)"
"  Sort Key: a.name"
"  ->   Nested Loop  (cost=1210.23..2596.16 rows=270 width=119)"
"Join Filter: (NOT st_within(st_buffer(a.way, 1::double
precision), (st_union(st_buffer(planet_osm_point.way, 1::double
precision)"
"  ->   Bitmap Heap Scan on planet_osm_point
(cost=15.39..1193.83 rows=405 width=100)"
"Recheck Cond: (tourism = 'viewpoint'::text)"
"->   Bitmap Index Scan on planet_osm_point_tourism
  (cost=0.00..15.29 rows=405 width=0)"
"  Index Cond: (tourism = 'viewpoint'::text)"
"->   Bitmap Heap Scan on planet_osm_point a
(cost=15.39..1193.83 rows=405 width=119)"
"  Recheck Cond: (tourism = 'viewpoint'::text)"
"  ->   Bitmap Index Scan on planet_osm_point_tourism
(cost=0.00..15.29 rows=405 width=0)"
"Index Cond: (tourism = 'viewpoint'::text)"

Yours, S.

2011/2/14 Pierre Racine:

I don't know if this would be fast but I would query those buffers which do not 
intersects with a Union of the layer... Something like:

SELECT id, name, elevation, geom
FROM peaktable A, (SELECT ST_Union(ST_Buffer(geom, 5)) AS geom FROM peaktable) B
WHERE NOT ST_Intersects(ST_Buffer(A.geom, 5), B.geom)

or maybe better:

SELECT id, name, elevation, geom
FROM peaktable A, (SELECT ST_Union(ST_Buffer(geom, 5)) AS geom FROM peaktable) B
WHERE NOT ST_DWithin(ST_Buffer(A.geom, 5), B.geom, 0)

But I haven't tried them.

Pierre


-Original Message-
From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-
boun...@postgis.refractions.net] On Behalf Of Stefan Keller
Sent: 14 février 2011 14:59
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Declarative SQL query for non-overlapping buffers 
given dense points?

I take the silence as an evidence that there is no declarative
solution to this challenge :->.
I think I have to write a stored procedure.

Yours, S.

2011/2/13 Stefan Keller:

Hi

Given a table of peaks with the fields id, name, elevation, geom I'd
like to write a query which returns to me only those (randomly
selected) peaks - i.e. peak buffers say with radius 5 kilometers -
which dont 'overlap'. This is motivated by a visualization use case
where label names should'nt overlap because of lack of visual space -
as well as because of low network capacity between db and client!

Any ideas on how to solve this with a declarative SQL query?

The only promising approach I found so far was is to define a subquery
with ranked buffers and then to select some out of these...

-S.


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] basic functionalities of PostGIS

[Birgit Laggner]

 Hi Apostolis,

sorry for not responding - I just didn't see your e-mail... Good, you 
reminded me!



On 02.02.2011 12:12, ΑΠΟΣΤΟΛΟΣ ΛΕΛΕΝΤΖΗΣ wrote:

Birgit, thanks for your reply.
Lately, i had no time...so now i deal with postgis!
Before, i run the sql file, i had noticed that you define your SRID 
code (31467). Here can i put the SRID code of my data??


Yes, that's right - at any instance where I am creating the geometry 
column you would have to change my SRID into the one of your data - If 
you don't want to do this, you also could change the function to 
automaticely recognize the SRID of your data:


Replace every 31467 in the functions with

st_srid('||schemaname||'.'||table_a||'

and then execute the changed functions again.

Then, i execute the sql file, and i type the following command for 
union two geometric tables:


select _pgunion('test1', 'poly1', 'id poly1', 'poly2', 'id poly2');

but, i got an error message:

ERROR: syntax error at or near "integer"
LINE 1: ...sec_poly1_poly2 (gid serial PRIMARY KEY, id poly1 integer, i...
^
QUERY: CREATE TABLE test1.intsec_poly1_poly2 (gid serial PRIMARY KEY, 
id poly1 integer, id poly2 integer );

CONTEXT: PL/pgSQL function "_pgunion" line 34 at EXECUTE statement

why?? from this message, i understand that the problem is the type of 
column id of the table poly1, is it??


I would guess the function has problems with the id names because they 
contain a space - try to rename them to id_poly1 or something like that...

could you help me for another time??
I suppose that i don't build with the right manner my data, so would 
be useful if you send me an example, namely, a backup file.

I will send you my test file in the next e-mail...

So, i 'll see the indexes, and anything else is required.

Thanks in advance
Apostolis


I hope everything will work fine :-)

Regards,

Birgit

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Update field from expression

[Birgit Laggner]

 No, probably not :-) - I was just fixed on the sub-select solution 
which is unnecessarily complicated...


On 28.01.2011 14:10, Ture Pålsson wrote:

2011/1/28 Birgit Laggner:

UPDATE permit_sites p_s SET huc = giddy.h_gid FROM (SELECT p.gid p_gid,
h.gid h_gid FROM permit_sites p inner join huc8 h on p.the_geom @ h.the_geom
  WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy WHERE p_s.gid=giddy.p_gid;

Is there something wrong with simply doing

UPDATE permit_sites p
SET huc = h.gid
FROM huc8 h
WHERE ST_Within(p.the_geom, h.the_geom)

?

(Assuming that is what the OP wanted to do, of course...)
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Update field from expression

[Birgit Laggner]

 Hi Nicolas,

you're right - I've made this mistake very often myself...

If there can be only one polygon for each point where st_within turns 
true for (which I assume), then this form of the query may solve that 
problem:


UPDATE permit_sites p_s SET huc = giddy.h_gid FROM (SELECT p.gid p_gid, h.gid 
h_gid FROM permit_sites p inner join huc8 h on p.the_geom @ h.the_geom
 WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy WHERE p_s.gid=giddy.p_gid;


I hope, I made no mistake...

Regards,

Birgit.


On 28.01.2011 10:55, Ture Pålsson wrote:

2011/1/28 Nicolas Ribot:


UPDATE permit_sites SET huc = giddy.gid FROM (SELECT h.gid FROM huc8 h,
permit_sites p WHERE ST_Within(p.the_geom,h.the_geom)) AS giddy;

Let me ask a rhetorical question: Which row from the inner SELECT gets
connected to which row of the table being updated?
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] why st_isclosed() is false?

[Birgit Laggner]

 Hallo Ralf,

thank you! I didn't notice that.  So, I should remove the end points in 
both linestrings or perhaps remove the linestrings at all... I will see...


Regards,

Birgit.


On 17.01.2011 16:10, Ralf Suhr wrote:

Hallo Birgit,

your Multilinestring can not be convert to a valid linestring. ST_Linemerge
give not a Linestring.
The parts 2 and 3 have the same start and endpoint

Gr
Ralf

Am Montag 17 Januar 2011, 15:51:49 schrieb Birgit Laggner:

   Dear list,

perhaps somebody could clear things for me:

If I run the query below, the result is "false". But I don't understand
why. I always thought, as long as there exists a continuous chain of
start and end points, a MultiLineString would be closed. Am I wrong
somehow?

Thanks and regards,

Birgit.


select st_isclosed(st_geomfromtext(
'MULTILINESTRING(
(3584442.63274253 5842172.41579578,
   3584358.9809 5842152.7361,
   3584355.23337321 5842153.70245931),
(3584355.23337321 5842153.70245931,
   3584355.23330909 5842153.70247585,
   3584355.23339452 5842153.70245382,
   3584355.23337321 5842153.70245931),
(3584355.23337321 5842153.70245931,
   3584348.03488658 5842155.55873215,
   3584345.68905341 5842155.07665686,
   3584343.82931858 5842154.69447615,
   3584340.46724669 5842152.9072508,
   3584338.14023057 5842151.67024508,
   3584328.33210257 5842146.45639614,
   3584009.05607027 5842072.59988407,
   3583887.98803815 5842042.75796404,
   3583790.05011806 5842106.6478841,
   3583790.050125842106.64788453,
   3583878.43617231 5842126.47539268,
   3584324.73120656 5842226.59207621,
   3584329.67415529 5842229.44269127,
   3584334.41005457 5842232.17390022,
   3584368.1119426  5842240.76423623,
   3584463.63629469 5842262.81966025,
   3584488.97911407 5842268.84731974),
(3584488.97911407 5842268.84731974,
   3584488.97978147 5842268.84747848,
   3584488.97978146 5842268.84747848,
   3584488.97911407 5842268.84731974),
(3584488.97911407 5842268.84731974,
   3584488.97890809 5842268.84727075,
   3584464.1099 5842262.3082,
   3584464.10990818 5842262.30817579,
   3584466.07027273 5842256.50678355,
   3584472.08934014 5842238.694295,
   3584472.08940751 5842238.69409565,
   3584489.545  5842187.037,
   3584489.5450305  5842187.03700481,
   3584489.54503071 5842187.03700417,
   3584526.86014482 5842192.92533864,
   3584514.30279074 5842190.12385218,
   3584495.65165472 5842186.30586817,
   3584480.67207071 5842180.87201217,
   3584458.64642469 5842176.18313362,
   3584458.64634269 5842176.18311617,
   3584442.63469631 5842172.41625542,
   3584442.63274253 5842172.41579578))',31467));

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] why st_isclosed() is false?

[Birgit Laggner]

 Dear list,

perhaps somebody could clear things for me:

If I run the query below, the result is "false". But I don't understand 
why. I always thought, as long as there exists a continuous chain of 
start and end points, a MultiLineString would be closed. Am I wrong somehow?


Thanks and regards,

Birgit.


select st_isclosed(st_geomfromtext(
'MULTILINESTRING(
(3584442.63274253 5842172.41579578,
 3584358.9809 5842152.7361,
 3584355.23337321 5842153.70245931),
(3584355.23337321 5842153.70245931,
 3584355.23330909 5842153.70247585,
 3584355.23339452 5842153.70245382,
 3584355.23337321 5842153.70245931),
(3584355.23337321 5842153.70245931,
 3584348.03488658 5842155.55873215,
 3584345.68905341 5842155.07665686,
 3584343.82931858 5842154.69447615,
 3584340.46724669 5842152.9072508,
 3584338.14023057 5842151.67024508,
 3584328.33210257 5842146.45639614,
 3584009.05607027 5842072.59988407,
 3583887.98803815 5842042.75796404,
 3583790.05011806 5842106.6478841,
 3583790.050125842106.64788453,
 3583878.43617231 5842126.47539268,
 3584324.73120656 5842226.59207621,
 3584329.67415529 5842229.44269127,
 3584334.41005457 5842232.17390022,
 3584368.1119426  5842240.76423623,
 3584463.63629469 5842262.81966025,
 3584488.97911407 5842268.84731974),
(3584488.97911407 5842268.84731974,
 3584488.97978147 5842268.84747848,
 3584488.97978146 5842268.84747848,
 3584488.97911407 5842268.84731974),
(3584488.97911407 5842268.84731974,
 3584488.97890809 5842268.84727075,
 3584464.1099 5842262.3082,
 3584464.10990818 5842262.30817579,
 3584466.07027273 5842256.50678355,
 3584472.08934014 5842238.694295,
 3584472.08940751 5842238.69409565,
 3584489.545  5842187.037,
 3584489.5450305  5842187.03700481,
 3584489.54503071 5842187.03700417,
 3584526.86014482 5842192.92533864,
 3584514.30279074 5842190.12385218,
 3584495.65165472 5842186.30586817,
 3584480.67207071 5842180.87201217,
 3584458.64642469 5842176.18313362,
 3584458.64634269 5842176.18311617,
 3584442.63469631 5842172.41625542,
 3584442.63274253 5842172.41579578))',31467));

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Transform WGS84 to UTM

[Birgit Laggner]

 Hi Lelo,

yes, normally it's very simple. If you know the SRID of the projection 
you want to transform your geometry to, you only have to use


ST_Transform(your_geometry_column, new_srid_number)

(see: 
http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html)


If you don't know the new SRID, I suggest a search at 
http://spatialreference.org/


Regards,

Birgit.

On 11.01.2011 20:01, Lelo wrote:

Hi All,

I'm starting to work with postgis and I have to transform geometries 
from WGS84 to UTM. Are there a simple way to do that using just the 
postgis?


Thanks for all

--
Rogério De Pieri  (Lelo)
Buscando melhorar a cada dia
Áudio, Hardware & Software


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] basic functionalities of PostGIS

[Birgit Laggner]

 Hi Apostolis,

as Andreas mentioned, I wrote a pl/pgsql function for the arcgis union 
which is still far from being perfect. I attach the sql file to this 
mail and you can try if it works for your data. If you want to use the 
function, then first, execute the create or replace function parts (I 
splitted the function into 6 parts: start(pgunion), intersection, no 
intersection, difference_a, difference_b and merge) and then call the 
functions with


select _pgunion('/schema name/', '/table1/', '/id table1/', '/table2/', 
'/id table2/');
select _pgintersection('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/intsec_table1_table2/');
select _pgnointersection('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/intsec_table1_table2/', 
'/nointsec_table1/', '/nointsec_table2/');
select _pgdifference_a(/'schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/diff_table1/');
select _pgdifference_b('/schema name/', '/table1/', '/id table1/', 
'/table2/', '/id table2/', '/diff_table2/');
select _pgmerge('/schema name/', '/id table1/', '/id table2/', 
'/intsec_table1_table2/', '/nointsec_table1/', '/nointsec_table2/', 
'/diff_table1/', '/diff_table2/', '/pgunion_table1_table2/');


(italic letters mean that you should replace these words by the names of 
your tables and id columns)


Your data should have
-gid column (column name is required) with data type integer or serial 
and btree index,
-id column (any name, name is passed with function call) with data type 
integer or serial and btree index,

-geometry column named the_geom with gist index

Both tables should be in the same schema (Certainly, this is changeable, 
but since you said you are a novice in postgis, I suppose you would 
rather use the functions in their current state).


If you don't want to have problems with polygon duplication, it would be 
wise to first make sure that you data does not contain duplicates. But 
my tests did show me, that it's very unlikely that my functions create 
more duplicates than there were before.


The functions will create a series of tables:
-intsec_/table1/_/table2/
-nointsec_/table1/
-nointsec_/table2/
-diff_/table1/
-/table1/_/table2/_diff_exceptions (contains polygons where the 
difference of table1-table2 threw an error)

-diff_/table2/
-/table2/_/table1/_diff_exceptions (contains polygons where the 
difference of table2-table1 threw an error)

-pgunion_/table1/_/table2/ (this is the result table!!)


If you need some more explanations, please ask.

I would be happy if somebody else (you??) would test my functions. I 
tested them for instance with tables of about 400.000 rows against 
15.000 rows and it ran for about 1-2 days.


Regards and success,

Birgit.



On 07.01.2011 22:11, ? ? wrote:

Andreas thank you for your reply!!
I am novice in postgis, and i would appreciate if you give me some 
useful advice..
Also, I am really greatful, if you send me the code to union two 
shapefiles!!
I don't want to use any more the arcgis desktop, and i understand that 
postgis is a very good solution for data collection and manipulation 
with many spatial functio


Apostolis


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
CREATE OR REPLACE  FUNCTION _pgunion(schemaname varchar(20), table_a varchar(50), a_id varchar(20), table_b varchar(50), b_id varchar(20)) RETURNS void AS 
$BODY$

 DECLARE

 intersection   varchar(60);
 nointersect_a  varchar(60);
 nointersect_b  varchar(60);
 diff_a varchar(60);
 diff_b varchar(60);
 result varchar(60);

 BEGIN

intersection := 'intsec_'||table_a||'_'||table_b;
nointersect_a :='nointsec_'||table_a;
nointersect_b :='nointsec_'||table_b;
diff_a :='diff_'||table_a;
diff_b :='diff_'||table_b;
result :='pgunion_'||table_a||'_'||table_b;

execute
'drop table if exists '||schemaname||'.'||intersection||';';
execute
'drop table if exists '||schemaname||'.'||nointersect_a||';';
execute
'drop table if exists '||schemaname||'.'||nointersect_b||';';
execute
'drop table if exists '||schemaname||'.'||diff_a||';';
execute
'drop table if exists '||schemaname||'.'||diff_b||';';
execute
'drop table if exists '||schemaname||'.'||result||';';


execute
'CREATE TABLE '||schemaname||'.'||intersection||' ('||
 'gid serial PRIMARY KEY, '||
 ''||a_id||' integer, '||
 ''||b_id||' integer '||
 ');';

execute
'select addgeometrycolumn('''||schemaname||''', '''||intersection||''',''the_geom'',31467,''POLYGON'',2);';

execute
'alter table '||schemaname||'.'||intersection||' drop constraint enforce_geotype_the_geom;';
 
execute
'CREATE TABLE '||schemaname||'.'||nointersect_a||' ('||
 'gid serial PRIMARY KEY, '||
 ''||a_id||' integer, '||
 ''||b_id||' integer '||
 ');';

execute
'select addgeometrycolumn('''||schemaname||''', '''||nointersect_a||''',''the_geom'',31467,''POLYGON'',2);';

Re: [postgis-users] Very slow intersection

[Birgit Laggner]

 Hi Ted,

I am not sure if this would help very much, but I always make an inner 
join on the bounding boxes of the geometries (as a sort of filter), like 
this:


create table countyShp as select cty.gid, 
st_intersection(cty.the_geom,cst.the_geom) as the_geom from 
countyShpWideBound as cty inner join uscoast as cst on cty.the_geom && 
cst.the_geom where st_intersects(cty.the_geom,cst.the_geom);


Are you sure, you have a gist-index on your geometries?

But, with tables with more than 50 rows, I get comparable run times 
like you.


Regards,

Birgit.


On 01.12.2010 15:30, Ted Rosenbaum wrote:

Hello,
I am looking to take the standard Tiger/Line Shapefile of US counties, 
which includes major waterways in the border of counties (especially 
an issue around the Great Lakes) and truncate the county polygons to 
exclude the area beyond the coastline.  I tried creating a new table 
based on two tables from two shapefiles -- one of the county polygons 
(from tiger/line) and one of the US coastline-- using the following code:
create table countyShp as select cty.gid, 
st_intersection(cty.the_geom,cst.the_geom) as the_geom from 
countyShpWideBound as cty, uscoast as cst where 
st_intersects(cty.the_geom,cst.the_geom)


I have indexes on the geometries in both tables, but this is taking 
hours to run (I had a power failure after about 6 hours and it was not 
finished running).


This seems like it should be a very simple and common issue, so I 
wanted to see if people could let me know either what I am doing wrong 
in my SQL statement or of alternative approaches to excluding areas of 
the coast from US county boundaries.


Thanks.

-
Ted Rosenbaum
Graduate Student
Department of Economics
Yale University



___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Iterating through large datasets

[Birgit Laggner]

 Hi Fabio,

I am not sure if this would really enhance the performance of our error 
check, but, I could think about a third option: Append all tables into 
one with an identifier for the type of information (e.g. roads, 
blocks,...). Then perform an iterating selfintersection for the new 
table and write your error log depending on the combination of type 
identifiers found during the selfintersection.
The advantage is that you are able to check the relations of all types 
in one step. The disadvantage might be that the appended table could be 
so big that the selfintersection has a poor performance again.


Regards,

Birgit.



On 24.09.2010 18:35, Fabio Renzo Panettieri wrote:

Hi, I have a question about performance.

I have to run a big number of validations against multiple tables, with
lots of records. Each table has 200k rows aprox.

They represent roads, blocks, buildings, landuses, infrastructure,
etc...

Also, I have a "errors" table, where the problems found are stored along
a simple description of the problem.

With plpgsql I have developed a large set of topological rules that
validates their relations eg. "blocks intersects building?", and create
a new entry in the errors table when needed.

My question is, how should I check each rule to ensure the best
performance?

Currently I get the new objects of each table (roads for example), and
check the relation with others. This way I ensure that roads are
iterated only once, but I'm not sure if it's the best way.

> From the top of my head, I think I can write rules that selects errors
and stores them. The only problem I see to this approach, is that all
the tables are going to be iterated many times.

I would like to know what do you think, which should I use? or if you
know a better way to do that.


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] problem to obtain intersection: TopologyException

[Birgit Laggner]

 Hi Salas,

did you check if your geometries are all valid (with function 
st_isvalid(geometry))?


Regards,

Birgit.


On 24.09.2010 16:33, salas wrote:

Hi,
I have a table 'municipio' with a geometric column (the_geom). I want 
to get a view with the resultant of the interception between the 
the_geom column and a polygon.

The query:
--
SELECT ST_Dump(ST_Intersection(the_geom, 
GeomFromText('MULTIPOLYGON(((177056.442675 283157.030268, 
283157.030268 192559.320783, 192559.320783 293446.551137, 
177056.442675 293446.551137, 177056.442675 283157.030268)))',2085)))

FROM municipio
WHERE the_geom IS NOT NULL
  AND ST_Intersects(the_geom, 
GeomFromText('MULTIPOLYGON(((177056.442675 283157.030268, 
283157.030268 192559.320783, 192559.320783 293446.551137, 
177056.442675 293446.551137, 177056.442675 283157.030268)))',2085)) = TRUE

return an error:

NOTICE:  TopologyException: found non-noded intersection between 
178970 311240, 178977 311221 and 178976 311224, 178971 311238 178972 
311234


ERROR:  GEOS Intersection() threw an error!
** Error **
ERROR: GEOS Intersection() threw an error!
SQL state: XX000

What's the problem 
The WHERE statemente ((the_geom, GeomFromText( ...) ) ensure the 
intersection

Best regards, Salas


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] ESRI union (analysis) in Postgis

[Birgit Laggner]

Hi Teresa,

I'm struggeling with the translation of the union (analysis) tool of
ArcGIS into PostGIS myself since a while. The problem is, that in
PostGIS, you always compare one row of the first data set with one row
of the second data set. That means, that interaction of more than 2
polygons is always a problem... I try to solve it by using loops written
with pl/pgsql. They loop through all polygons of one data set and
compare every single polygon with all polygons of the other data set.
All polygons of the second data set that intersect with the reviewed
polygon of the first data set are written into a new table. As for the
difference part they are bundled with st_union and then st_difference is
performed with the polygon of the first data set. Regarding the
intersection part, st_intersection is performed for all combinations of
polygon (data set 1) and the intersecting polygons of data set 2. Then
the resulting polygons have to be intersected again in a loop until no
new intersections occur.

If you are interested, I could share the current state of my
experimentation with you... But don't expect too much - as Ralf already
said, allocation of attributes (and not only that) is very difficult.
But, unfortunately, most datasources, I have to use, contain overlapping
polygons. Therefore, I have to deal with that situation somehow as you
may have to do, too.

Regards,

Birgit.
> Hi Teresa,
>
> you can't get a intersection from more then two polygons at a time. Think 
> about the attributes you get. The attributes are from poly1 and or poly2. The 
> solution is to use only datasources with non overlaping polygons.
>
> Gr
> Ralf
>
> Am Donnerstag 08 Juli 2010, 09:27:06 schrieb Teresa Fazio:
>   
>> > The solution you propose is valid to get polygons describing
>> > intersections between two polygons, but I cannot figure out how to
>> > obtain polygons describing intersections between more than two polygons
>> > like the central polygon drawn in the shape TEST_Union.
>> > I repeat that the TEST_Union shapefile was simply obtained using the
>> > "union (analysis)" tool in ESRI ArcGIS.
>> > 
>> > Any other help?
>> > Thank you.
>> > 
>> > ___
>> > postgis-users mailing list
>> > postgis-users@postgis.refractions.net
>> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>> 
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>   
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] OT: SQL problem/challenge

[Birgit Laggner]

Hi Stephen,

I don't know if this is feasible with your data, but if you absolutely
want to avoid the use of temporary tables, you could simply replace the
A, B and C in the WHERE clause of your not working SQL query by the
corresponding VALUES clauses.

Regards,

Birgit.


On 05.06.2010 16:33, Stephen Woodbridge wrote:
> Stephen Woodbridge wrote:
>> Hi,
>>
>> I have a challenging SQL problem, that I thought some of you might be
>> able and interested to try and solve.
>>
>> My problem is that I need to compute some number of separate and
>> costly queries as represented VALUES clauses below. And I need to
>> eliminate the overlaps in the adjacent sub-selects, but not globally
>> from the results.
>>
>> So in this example
>> gid 6 and 7 would get eliminated from A and B and
>> gid 11 and 12 would get eliminated from B and C but
>> gid 1 would not get eliminated.
>>
>> Sadly I'm currently working on Postgresql 8.3.9 other wise I think
>> using the WITH clause in 8.4 for would help.
>>
>> So at this point I'm kind of thinking that the only way I can do this
>> with 8.3 is to  "create temp table leg1 as select ..."  and then work
>> with the temp tables. This will be running in a plpgsql function it
>> that matters.
>>
>> I would be interested in ideas/examples for both 8.3 and 8.4.
>>
>> Thanks,
>>   -Steve
>>
>> Here is a piece of not working SQL:
>>
>> select *
>> from (
>>   select gid, aa from
>> (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa)
>>   union all
>>   select gid, aa from
>> (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B
>> (gid, aa)
>>   union all
>>   select gid, aa from
>> (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as
>> C (gid, aa)
>> ) as foo
>> -- the where clause does not work, specifically the sub-select
>> -- this does global elimination,
>> -- and it needs to be locally between sub-selects
>> where gid not in (
>> (select gid from A intersect select gid from B)
>>union
>> (select gid from B intersect select gid from C)
>>   )
>
> With a little more work and I was able to get this to work with temp
> tables, although I would like to avoid temp tables if possible.
>
> create temp table A on commit drop as
>   select gid, aa from
> (values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7)) as A (gid, aa);
>
> create temp table B on commit drop as
>   select gid, aa from
> (values (7,7),(6,6),(8,8),(9,9),(10,10),(11,11),(12,12)) as B
> (gid, aa);
>
> create temp table C on commit drop as
>   select gid, aa from
> (values (12,12),(11,11),(13,13),(14,14),(15,15),(16,16),(1,1)) as
> C (gid, aa);
>
> select * from (
>   select * from A where gid not in ((select gid from A intersect
> select gid from B))
> union all
>   select * from B where gid not in (
>( select gid from A intersect select gid from B
>  union
>  select gid from B intersect select gid from C
>)
>  )
>   union all
>   select * from C where gid not in ((select gid from B intersect
> select gid from C))
> ) as foo;
>
> So I assume, that in 8.4 that I can convert the temp tables to WITH
> clauses preceding the select. Is this the best that can be done with 8.3?
>
> -Steve
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] st_area and 0.001953125 (Possible Bug)

[Birgit Laggner]

Hi Marc,

I only wanted to remark that not only small polygons are affected by
this phenomenon - all areas I calculated with st_area() are multiples of
0.001953125 (what's not as it should be as appears to me).

I would have commented this at the link you attached, but I don't know
how (do I have to login or anything else?).

Regards,

Birgit.


On 15.04.2010 15:51, Marc Jansen wrote:
> Hi Birgit,
>
> good catch. I am pretty sure this is some sort of a bug, possibly
> related to 32-/64-bit architecture.
>
> I opened a bug [1], and cced Birgit. Add comments there as you dig
> deeper.
>
> Regards,
> Marc
>
> [1] http://trac.osgeo.org/postgis/ticket/503
>
>
> Birgit Laggner wrote:
>> Hi Maxime,
>>
>> well, this would argue against the guess from Marc regarding the
>> versions change from 1.4.0 to 1.4.1 (I can't imagine that 1.5.1 has
>> errors that were already debugged in 1.4.1). What catches my eye is that
>> all systems that show the error are 64-bit systems and the one from Marc
>> providing the true result is a 32-bit system. Maybe this could be
>> leading to an explanation??
>>
>> Thanks  and regards,
>>
>> Birgit.
>>
>>
>> On 15.04.2010 13:48, Maxime van Noppen wrote:
>>  
>>> On 04/15/2010 01:37 PM, Marc Jansen wrote:
>>>  
>>>> Hi Birgit,
>>>>
>>>> I can confirm your results on
>>>>   
>>> To help tracking down the bug: I have the same problem (areas
>>> 0.001953125 or 0 for the query on the pastebin) on :
>>>
>>> PostgreSQL 8.4.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
>>> 4.4.3 20100316 (prerelease), 64-bit
>>>
>>> POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September
>>> 2009" LIBXML="2.7.6" USE_STATS
>>>
>>>   
>> ___
>> postgis-users mailing list
>> postgis-users@postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>   
>
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] st_area and 0.001953125

[Birgit Laggner]

Hi Maxime,

well, this would argue against the guess from Marc regarding the
versions change from 1.4.0 to 1.4.1 (I can't imagine that 1.5.1 has
errors that were already debugged in 1.4.1). What catches my eye is that
all systems that show the error are 64-bit systems and the one from Marc
providing the true result is a 32-bit system. Maybe this could be
leading to an explanation??

Thanks  and regards,

Birgit.


On 15.04.2010 13:48, Maxime van Noppen wrote:
> On 04/15/2010 01:37 PM, Marc Jansen wrote:
>   
>> Hi Birgit,
>>
>> I can confirm your results on
>> 
> To help tracking down the bug: I have the same problem (areas
> 0.001953125 or 0 for the query on the pastebin) on :
>
> PostgreSQL 8.4.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
> 4.4.3 20100316 (prerelease), 64-bit
>
> POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September
> 2009" LIBXML="2.7.6" USE_STATS
>
>   
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] st_area and 0.001953125

[Birgit Laggner]

Hi Marc,

here my full versions list for all 3 servers:

"POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS"
"PostgreSQL 8.2.13 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(SUSE Linux) 4.3.2 [gcc-4_3-branch revision 141291]"

"POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September
2009" USE_STATS (procs from 1.3.3 need upgrade)"
"PostgreSQL 8.2.15 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(SUSE Linux) 4.4.1 [gcc-4_4-branch revision 150839]"

"POSTGIS="1.4.0" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS"
"PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.4.1 [gcc-4_4-branch revision 150839], 64-bit"

I will try to upgrade my local postgis installation in order to verify
your guess (postgis version dependance).

Thanks again and regards,

Birgit.


On 15.04.2010 13:37, Marc Jansen wrote:
> Hi Birgit,
>
> I can confirm your results on
>
> POSTGIS="1.3.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct 2004"
> USE_STATS
>
> 0.001953125 or 0 for the given examples.
>
> I can only guess that it is some sort of silent change in between
> 1.4.0 and 1.4.1. Could that be? Do have the possibility to upgrade
> your Postgis installation? BTW which version of Postgresql are you using?
>
> The confirmed error is on "PostgreSQL 8.2.4 on x86_64-pc-linux-gnu,
> compiled by GCC cc (GCC) 4.1.2 20061115 (prerelease) (Debian
> 4.1.1-21)", the correct results are obtained from "PostgreSQL 8.4.2 on
> i486-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu
> 4.4.1-4ubuntu8) 4.4.1, 32-bit" (selected using "SELECT version();")
>
> Regards,
> Marc
>
>
> Birgit Laggner wrote:
>> Hi Marc,
>>
>> your results look wonderful, but I don't know how you achieve them. Here
>> my results for your pgsql statement and the postgis_fullversion for the
>> 3 servers I tested the statement on:
>>
>> "POSTGIS="1.4.0" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
>> 2008" USE_STATS"
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0
>> 0.001953125
>> 0
>> 0.001953125
>> 0.001953125
>> 0.001953125
>>
>>
>> "POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
>> 2008" USE_STATS"
>> 0
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0
>> 0.001953125
>>
>>
>>
>> "POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September
>> 2009" USE_STATS (procs from 1.3.3 need upgrade)"
>> 0
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0.001953125
>> 0
>> 0.001953125
>>
>>
>> Any suggestions why my results are so different from yours?
>>
>> Thanks and regards,
>>
>> Birgit.
>>
>>
>>
>>
>> On 15.04.2010 11:17, Marc Jansen wrote:
>>  
>>> Hi Birgit,
>>>
>>> I have different results here:
>>>
>>> http://osgeo.pastebin.com/dhPpqH9J
>>>
>>> Regards,
>>> Marc
>>>
>>>
>>>
>>> Birgit Laggner wrote:
>>>
>>>> Yes, sure... Here some of my geometries with st_area=0.001953125
>>>> (1/2^9):
>>>>
>>>> "SRID=31467;POLYGON((3508395.5093 5910738.8973,3508505.2864
>>>> 5910690.8533,3508300.641 5910780.4164,3508395.5093 5910738.8973))"
>>>> "SRID=31467;POLYGON((3508395.5093 5949092.7294,3508333.8642
>>>> 5949090.138,3508411.1808 5949093.3882,3508395.5093 5949092.7294))"
>>>> "SRID=31467;POLYGON((3498246.2563 5815493.2389,3498246.2472
>>>> 5815493.1259,3498246.2094 5815493.1202,3498246.2563 5815493.2389))"
>>>> "SRID=31467;POLYGON((3539635.9601 5832797.6616,3539635.9574
>>>> 5832797.6613,3539635.9565 5832797.6851,3539635.9601 5832797.6616))"
>>>> "SRID=31467;POLYGON((3546842.9496103 5778962.75289926,3546842.9761781
>>>> 5778962.61321963,3546842.976 5778962.613,3546842.9496103
>>>> 5778962.75289926))"
>>>> "SRID=31467;POLYGON((3427425.5879 5841806.726,3427416.0171
>>>> 5841792.2466,3427416.0170324

Re: [postgis-users] st_area and 0.001953125

[Birgit Laggner]

Hi Marc,

your results look wonderful, but I don't know how you achieve them. Here
my results for your pgsql statement and the postgis_fullversion for the
3 servers I tested the statement on:

"POSTGIS="1.4.0" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS"
0.001953125
0.001953125
0.001953125
0.001953125
0
0.001953125
0
0.001953125
0.001953125
0.001953125


"POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.6.1, 21 August
2008" USE_STATS"
0
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0
0.001953125



"POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September
2009" USE_STATS (procs from 1.3.3 need upgrade)"
0
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0.001953125
0
0.001953125


Any suggestions why my results are so different from yours?

Thanks and regards,

Birgit.




On 15.04.2010 11:17, Marc Jansen wrote:
> Hi Birgit,
>
> I have different results here:
>
> http://osgeo.pastebin.com/dhPpqH9J
>
> Regards,
> Marc
>
>
>
> Birgit Laggner wrote:
>> Yes, sure... Here some of my geometries with st_area=0.001953125
>> (1/2^9):
>>
>> "SRID=31467;POLYGON((3508395.5093 5910738.8973,3508505.2864
>> 5910690.8533,3508300.641 5910780.4164,3508395.5093 5910738.8973))"
>> "SRID=31467;POLYGON((3508395.5093 5949092.7294,3508333.8642
>> 5949090.138,3508411.1808 5949093.3882,3508395.5093 5949092.7294))"
>> "SRID=31467;POLYGON((3498246.2563 5815493.2389,3498246.2472
>> 5815493.1259,3498246.2094 5815493.1202,3498246.2563 5815493.2389))"
>> "SRID=31467;POLYGON((3539635.9601 5832797.6616,3539635.9574
>> 5832797.6613,3539635.9565 5832797.6851,3539635.9601 5832797.6616))"
>> "SRID=31467;POLYGON((3546842.9496103 5778962.75289926,3546842.9761781
>> 5778962.61321963,3546842.976 5778962.613,3546842.9496103
>> 5778962.75289926))"
>> "SRID=31467;POLYGON((3427425.5879 5841806.726,3427416.0171
>> 5841792.2466,3427416.01703247 5841792.24664866,3427425.5879
>> 5841806.726))"
>> "SRID=31467;POLYGON((3538420.34038636 5912791.09822265,3538420.31141462
>> 5912790.97224757,3538420.3113 5912790.9723,3538420.34038636
>> 5912791.09822265))"
>> "SRID=31467;POLYGON((3577970.8916 5729534.987,3577827.5551
>> 5729621.0532,3577805.6282 5729634.2192,3577970.8916 5729534.987))"
>> "SRID=31467;POLYGON((3472882.79775403 5870816.50310222,3472882.7978
>> 5870816.5031,3472875.19541818 5870717.64922727,3472882.79775403
>> 5870816.50310222))"
>> "SRID=31467;POLYGON((3557563.34513562 5791040.4530635,3557563.44511383
>> 5791040.49625184,3557563.3452 5791040.4529,3557563.34513562
>> 5791040.4530635))"
>>
>> st_area=0.00390625 (1/2^8):
>>
>> "SRID=31467;POLYGON((3570365.59720136 5742710.02924098,3570358.1909
>> 5742719.6418,3570358.191599 5742719.64149272,3570365.59720136
>> 5742710.02924098))"
>> "SRID=31467;POLYGON((3561579.38938441 5905129.43679735,3561545.6658
>> 5905163.3222,3561522.09118149 5905187.00889311,3561545.6653
>> 5905163.3228,3561579.38938441 5905129.43679735))"
>> "SRID=31467;POLYGON((3552150.9548 5742650.4051,3552168.62992722
>> 5742646.68243639,3552168.6301 5742646.6818,3552150.9548 5742650.4051))"
>> "SRID=31467;POLYGON((3561749.8571 5865749.873,3561799.5277
>> 5865766.3108,3561799.52805235 5865766.31075114,3561749.8571
>> 5865749.873))"
>> "SRID=31467;POLYGON((3473864.032 5873365.3502,3473872.3622
>> 5873371.4589,3473879.97516418 5873377.10501052,3473872.3622
>> 5873371.4584,3473864.032 5873365.3502))"
>> "SRID=31467;POLYGON((3560788.59448517 5748665.31989661,3560796.5561
>> 5748667.1403,3560807.5257 5748669.0521,3560796.5561
>> 5748667.1398,3560788.59448517 5748665.31989661))"
>> "SRID=31467;POLYGON((3456586.93009402 5872050.7320991,3456603.71148126
>> 5872024.70635152,3456586.9299 5872050.7319,3456586.93009402
>> 5872050.7320991))"
>> "SRID=31467;POLYGON((3573125.32168799 5906771.92751555,3573125.3215
>> 5906771.9274,3573112.75787677 5906796.71385397,3573112.7581
>> 5906796.7139,3573125.32168799 5906771.92751555))"
>> "SRID=31467;POLYGON((3575905.7401 5719920.9669,3575904.88200387
>> 5719873.25691506,3575904.882 5719873.2572,3575905.7401 5719920.9669))"
>> "SRID=31467;POLYGON((3547343.0802 5741914.6939,3547341.3108
>> 5741921.4379,3547339.1261 5741930.6872,3547336.9408
>> 5741937.6228,3547335.58835 5741941.09235,3547336.94

Re: [postgis-users] st_area and 0.001953125

[Birgit Laggner]

Yes, sure... Here some of my geometries with st_area=0.001953125 (1/2^9):

"SRID=31467;POLYGON((3508395.5093 5910738.8973,3508505.2864
5910690.8533,3508300.641 5910780.4164,3508395.5093 5910738.8973))"
"SRID=31467;POLYGON((3508395.5093 5949092.7294,3508333.8642
5949090.138,3508411.1808 5949093.3882,3508395.5093 5949092.7294))"
"SRID=31467;POLYGON((3498246.2563 5815493.2389,3498246.2472
5815493.1259,3498246.2094 5815493.1202,3498246.2563 5815493.2389))"
"SRID=31467;POLYGON((3539635.9601 5832797.6616,3539635.9574
5832797.6613,3539635.9565 5832797.6851,3539635.9601 5832797.6616))"
"SRID=31467;POLYGON((3546842.9496103 5778962.75289926,3546842.9761781
5778962.61321963,3546842.976 5778962.613,3546842.9496103 5778962.75289926))"
"SRID=31467;POLYGON((3427425.5879 5841806.726,3427416.0171
5841792.2466,3427416.01703247 5841792.24664866,3427425.5879 5841806.726))"
"SRID=31467;POLYGON((3538420.34038636 5912791.09822265,3538420.31141462
5912790.97224757,3538420.3113 5912790.9723,3538420.34038636
5912791.09822265))"
"SRID=31467;POLYGON((3577970.8916 5729534.987,3577827.5551
5729621.0532,3577805.6282 5729634.2192,3577970.8916 5729534.987))"
"SRID=31467;POLYGON((3472882.79775403 5870816.50310222,3472882.7978
5870816.5031,3472875.19541818 5870717.64922727,3472882.79775403
5870816.50310222))"
"SRID=31467;POLYGON((3557563.34513562 5791040.4530635,3557563.44511383
5791040.49625184,3557563.3452 5791040.4529,3557563.34513562
5791040.4530635))"

st_area=0.00390625 (1/2^8):

"SRID=31467;POLYGON((3570365.59720136 5742710.02924098,3570358.1909
5742719.6418,3570358.191599 5742719.64149272,3570365.59720136
5742710.02924098))"
"SRID=31467;POLYGON((3561579.38938441 5905129.43679735,3561545.6658
5905163.3222,3561522.09118149 5905187.00889311,3561545.6653
5905163.3228,3561579.38938441 5905129.43679735))"
"SRID=31467;POLYGON((3552150.9548 5742650.4051,3552168.62992722
5742646.68243639,3552168.6301 5742646.6818,3552150.9548 5742650.4051))"
"SRID=31467;POLYGON((3561749.8571 5865749.873,3561799.5277
5865766.3108,3561799.52805235 5865766.31075114,3561749.8571 5865749.873))"
"SRID=31467;POLYGON((3473864.032 5873365.3502,3473872.3622
5873371.4589,3473879.97516418 5873377.10501052,3473872.3622
5873371.4584,3473864.032 5873365.3502))"
"SRID=31467;POLYGON((3560788.59448517 5748665.31989661,3560796.5561
5748667.1403,3560807.5257 5748669.0521,3560796.5561
5748667.1398,3560788.59448517 5748665.31989661))"
"SRID=31467;POLYGON((3456586.93009402 5872050.7320991,3456603.71148126
5872024.70635152,3456586.9299 5872050.7319,3456586.93009402
5872050.7320991))"
"SRID=31467;POLYGON((3573125.32168799 5906771.92751555,3573125.3215
5906771.9274,3573112.75787677 5906796.71385397,3573112.7581
5906796.7139,3573125.32168799 5906771.92751555))"
"SRID=31467;POLYGON((3575905.7401 5719920.9669,3575904.88200387
5719873.25691506,3575904.882 5719873.2572,3575905.7401 5719920.9669))"
"SRID=31467;POLYGON((3547343.0802 5741914.6939,3547341.3108
5741921.4379,3547339.1261 5741930.6872,3547336.9408
5741937.6228,3547335.58835 5741941.09235,3547336.9408
5741937.6233,3547339.1261 5741930.6877,3547341.3108
5741921.4384,3547343.0802 5741914.6939))"

st_area=0.0078125 (1/2^7):

"SRID=31467;POLYGON((3650622.49435981 5875166.19247976,3650619.8317
5875115.4098,3650622.4941 5875166.1925,3650622.49435981 5875166.19247976))"
"SRID=31467;POLYGON((3568389.67127267 5851488.1421237,3568389.6712
5851488.1422,3568495.5728 5851570.3678,3568389.67127267 5851488.1421237))"
"SRID=31467;POLYGON((3571954.5197 5753275.44491658,3571927.0019
5753378.6038,3571954.5199 5753275.445,3571954.5197 5753275.44491658))"
"SRID=31467;POLYGON((3555658.1082 5728053.622,3555651.679
5728050.4778,3555644.8351 5728048.1717,3555634.0498
5728047.1242,3555644.8351 5728048.1723,3555651.679
5728050.4783,3555658.1082 5728053.622))"
"SRID=31467;POLYGON((3555058.35805504 5715181.02070526,3555108.2704
5715190.4028,3555058.3581 5715181.0204,3555058.35805504 5715181.02070526))"
"SRID=31467;POLYGON((3395315.40197822 5840656.87971937,3395229.2098
5840619.9673,3395315.4019 5840656.8799,3395315.40197822 5840656.87971937))"
"SRID=31467;POLYGON((3573074.18310709 5740403.73582028,3573074.183
5740403.7363,3573096.5482 5740407.9711,3573074.18310709 5740403.73582028))"
"SRID=31467;POLYGON((3544884.936 5844897.712,3544851.31613268
5844807.24085704,3544851.316 5844807.241,3544884.936 5844897.712))"
"SRID=31467;POLYGON((3576480.5538 5704180.1479,3576466.1052
5704170.6652,3576450.375 5704162.5361,3576466.1052
5704170.6657,3576480.5538 5704180.1479))"
"SRID=31467;POLYGON((3576450.375 5704162.5361,3576437.7542
5704156.0998,3576421.8422 5704149.326,3576437.7542
5704156.1003,3576450.375 5704162.5361))"

Just a few examples... As I said, all 

[postgis-users] st_area and 0.001953125

[Birgit Laggner]

Dear list,

when I use st_area(), I always get multiples of 0.001953125 (1/2^9) as a
result. Can anybody explain why?

Thanks and regards,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] st_max_distance - This function is unimplemented yet

[Birgit Laggner]

Hallo Nicklas,

a, ok, this explains everything...

Thanks a lot! Regards,

Birgit.


On 07.04.2010 14:50, Nicklas Avén wrote:
> Hallo Birgit
>  
> The max distance function was actually not implemented until version 1.5
> There were some ideas about the function earlier that by some reason
> made the sql-part of the function exist.
> But now it is there, so the recomendation is, upgrade to PostGIS
> 1.5.1. The function has also changed name so now it is called
> ST_Maxdistance
> http://postgis.org/documentation/manual-1.5/ST_MaxDistance.html
>  
> Hope that helps
> Nicklas
>
> 2010-04-07 Birgit Laggner wrote:
>
> Dear list,
> >
> >when I try to use the function st_max_distance(), I get the following
> error:
> >
> >ERROR: This function is unimplemented yet
> >SQL Status:XX000
> >
> >I am using PostGIS Version 1.3.6 and the geometries, I try to get the
> >max distance for, are linestrings.
> >St_max_distance and max_distance are both listed in my functions list in
> >the postgresql database.
> >
> >Am I doing something wrong?
> >
> >Regards,
> >
> >Birgit.
> >___
> >postgis-users mailing list
> >postgis-users@postgis.refractions.net
> >http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] st_max_distance - This function is unimplemented yet

[Birgit Laggner]

Dear list,

when I try to use the function st_max_distance(), I get the following error:

ERROR: This function is unimplemented yet
SQL Status:XX000

I am using PostGIS Version 1.3.6 and the geometries, I try to get the
max distance for, are linestrings.
St_max_distance and max_distance are both listed in my functions list in
the postgresql database.

Am I doing something wrong?

Regards,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] plpgsql - problem using variable schema and table names

[Birgit Laggner]

t(a_id)||',
   the_geom)
   SELECT
aid,
the_geom
   from tmp2;';

  execute
  'truncate table tmp2;';

  END IF;
 
 RAISE NOTICE 'Tabelle 1, Difference-Polygon % ist fertig. ', i ;

 ELSE RAISE NOTICE 'Kein Difference berechnet. ';

 END IF;

 END LOOP; --END LOOP 1

  execute
 'drop table '||schemaname||'.tmp1;';

  execute
 'drop table tmp2;';

 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION _laggner_b_pgdifference_auto_a(
 schemaname varchar(20),
 table_a varchar(50),
 a_id varchar(20),
 table_b varchar(50),
 b_id varchar(20),
 diff_a varchar(60))
OWNER TO postgres;


On 22.12.2009 19:46, Kevin Neufeld wrote:
> Hi Birgit,
>
> Hmmm, it looks like you have a few errors in your script.
> Comments inline.
>
>
> Birgit Laggner wrote:
>> Hi Kevin,
>>
>> for the defining of n, your suggestions works perfectly well. But in the
>> next step, I try to assign a record as an intersection of 2 tables. I
>> tried to solve this with a nested loop. But the problem is, after
>> assigning the records for table a and b, I can't address certain fields
>> within the records anymore, which would be necessary for the
>> intersection loop.
>>
>> By now, the part in question of my function looks like this (r_a, r_b
>> are declared as record, sql_a, sql_b are declared as text):
>>
>>   sql_a := 'select gid, '||a_id||' as '||a_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_a);
>
>
> I'm assuming, based on your original post, that 'a_id' is a parameter
> you wish to pass as a parameter to the function. You may want to
> quote_ident it as well to properly handle special characters, etc that
> may occur in the column name.
>
>>
>>   raise debug '%', sql_a;
>>   raise notice '%', sql_a;
>>
>>   sql_b := 'select gid, '||b_id||' as '||b_id||', the_geom from
>> '||quote_ident(schemaname)||'.'||quote_ident(table_b);
>
> Same as for sql_a.
>
>>
>>   raise debug '%', sql_b;
>>
>>  FOR r_a in execute sql_a LOOP
>>
>>  FOR r_b in execute sql_b LOOP
>>
>>  FOR recordset_object IN execute 'select
>>'||quote_ident(r_a.a_id) ||',
>>'||quote_ident(r_b.b_id) ||',
>>ST_intersection('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') AS the_geom
>>   WHERE st_intersects('||quote_ident(r_a.the_geom)||',
>> '||quote_ident(r_b.the_geom)||') and
>> '||quote_ident(r_a.the_geom)||' && '||quote_ident(r_b.the_geom)
>>
>
> Ok.  Here r_a is referencing a field called a_id.  So unless you've
> declared the text variable a_id to be 'a_id' (or passed it as a
> parameter with the same name), you'll get the error you see below.  In
> the sql_a declaration above, change the select clause to 'select gid,
> ' || quote_ident(a_id) || ' AS a_id, the_geom from '...  Similar for
> sql_b.
>
> Also, you are dereferencing the geometry fields which would be very
> slow.  Your call to quote_ident(r_a.the_geom) is taking the geometry
> field in record r_a and casting the whole thing to text so it can be
> used in the textual execute statement.
>
>
>>  LOOP
>>
>>   execute
>>   'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
>> ''||b_id||'', the_geom) '||
>>'VALUES ( '||
>> ''||recordset_object||'.''||a_id||'', '||
>> ''||recordset_object||'.''||b_id||'', '||
>> ''||recordset_object||'.the_geom);';
>>
>>  END LOOP;
>>  END LOOP;
>>  END LOOP;
>>
>> And the error message, I get, is:
>>
>> ERROR:  Record »r_a« hat no field »a_id«
>>
>> Any ideas, how to handle this?
>>
>> Thanks a lot and merry christmas,
>>
>> Birgit.
>>
>
>
> This seems wy too complicated and I don't think passing the
> geometry around like that will work for you.  If I may suggest, I
> think you could replace all three LOOPs with a single sql statement
> (logically, it looks like it should do the same thing):
>
> CREATE OR REPLACE  FUNCTION _laggner_b_pgintersection(
>   schemaname varchar(20),
>   table_a varchar(50),
>   a_id varchar(20),
>   table_b varchar(50),
>   b_id varchar(20),
>   intersection varchar(60)) RETURNS void AS
> $BODY$
>
> BEGIN
>   EXECUTE
> 'INSERT INTO ' ||
>quote_ident(schemaname) || '.' || quote_ident(intersection) ||
>  '(a.' || quote_ident(a_id) || ', b.' || quote_ident(b_id)
> ||', ST_Intersection(a.the_geom, b.the_geom) ' ||
> 'FROM ' || quote_ident(schemaname) || '.' || quote_ident(table_a)
> || ' AS a, ' ||
>quote_ident(schemaname) || '.' || quote_ident(table_b)
> || ' AS b ' ||
> 'WHERE ST_Intersects(a.the_geom, b.the_geom)';
> END
> $BODY$
>
> The only thing that is quoted here are the schema, table, and column
> names.  The query is parsed once by the query planner and then
> executed.  We're not casting geometries to text and back into the
> database again.  Is there a particular reason you broke out query into
> 3 main LOOPs?
>
> I hope this helps.
>
> You too, have a very Merry Christmas.
> Cheers,
> Kevin
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] plpgsql - problem using variable schema and table names

[Birgit Laggner]

Hi Kevin,

for the defining of n, your suggestions works perfectly well. But in the
next step, I try to assign a record as an intersection of 2 tables. I
tried to solve this with a nested loop. But the problem is, after
assigning the records for table a and b, I can't address certain fields
within the records anymore, which would be necessary for the
intersection loop.

By now, the part in question of my function looks like this (r_a, r_b
are declared as record, sql_a, sql_b are declared as text):

  sql_a := 'select gid, '||a_id||' as '||a_id||', the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_a);

  raise debug '%', sql_a;
  raise notice '%', sql_a;

  sql_b := 'select gid, '||b_id||' as '||b_id||', the_geom from
'||quote_ident(schemaname)||'.'||quote_ident(table_b);

  raise debug '%', sql_b;

 FOR r_a in execute sql_a LOOP

 FOR r_b in execute sql_b LOOP

 FOR recordset_object IN execute 'select
   '||quote_ident(r_a.a_id) ||',
   '||quote_ident(r_b.b_id) ||',
   ST_intersection('||quote_ident(r_a.the_geom)||',
'||quote_ident(r_b.the_geom)||') AS the_geom
  WHERE st_intersects('||quote_ident(r_a.the_geom)||',
'||quote_ident(r_b.the_geom)||') and
'||quote_ident(r_a.the_geom)||' && '||quote_ident(r_b.the_geom)

 LOOP

  execute
  'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
''||b_id||'', the_geom) '||
   'VALUES ( '||
''||recordset_object||'.''||a_id||'', '||
''||recordset_object||'.''||b_id||'', '||
''||recordset_object||'.the_geom);';

 END LOOP;
 END LOOP;
 END LOOP;

And the error message, I get, is:

ERROR:  Record »r_a« hat no field »a_id«

Any ideas, how to handle this?

Thanks a lot and merry christmas,

Birgit.



On 17.12.2009 22:41, Kevin Neufeld wrote:
> Small typo ... it's not
> RAISE DEBUG '%s', sql;
> but
> RAISE DEBUG '%', sql;
>
> Kevin Neufeld wrote:
>> http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
>>
>>
>> Try:
>>
>> FOR target IN EXECUTE text_expression LOOP
>>...
>> END LOOP;
>>
>>
>>
>> I usually do something like this:
>>
>> DECLARE
>>   ...
>>   schemaname text := 'myschema';
>>   tablename text := 'mytable';
>>   sql text;
>>   r record;
>>
>> BEGIN;
>>   sql := 'SELECT ... FROM ' || quote_ident(schemaname) || '.' ||
>> quote_ident(tablename);
>>   RAISE DEBUG '%s', sql;
>>
>>   FOR r IN EXECUTE sql LOOP
>> ...
>>   END LOOP
>> END;
>>
>> -- Kevin
>>
>> Birgit Laggner wrote:
>>> Yes, I have noticed that, but I don't know how to do that,
>>> especially at defining my n (loop end point variable). I tried
>>> various versions (also with execute), but without success.
>>>
>>> Birgit.
>>>
>>> On 17.12.2009 16:38, David William Bitner wrote:
>>>> Birgit,
>>>>
>>>> The problem may be that you are creating a varchar variable for
>>>> your schema name and then you are trying to use it in an instance
>>>> that is expecting a database object. Anytime you are trying to
>>>> insert variables as database objects, you need to construct your
>>>> query as a string and use execute similar as to how you are
>>>> creating your insert statement.
>>>>
>>>> David
>>>>
>>>> On Thu, Dec 17, 2009 at 9:27 AM, Birgit Laggner
>>>> mailto:birgit.lagg...@vti.bund.de>>
>>>> wrote:
>>>>
>>>> Dear list,
>>>>
>>>> I am trying to generalize a pl/pgsql function I have written (see
>>>> below). I would like to define schema and table names, as well as
>>>> certain column names, in the function call (as in the PostGIS
>>>> function
>>>> AddGeometryColumn) in order to use them to define schema and table
>>>> names
>>>> and everything else within the function queries.
>>>>
>>>> My problem is, that postgres doesn't recognize the defined
>>>> variable
>>>> names if I call them in a FROM clause or INSERT INTO.  This is the
>>>> error
>>>> messa

Re: [postgis-users] plpgsql - problem using variable schema and table names

[Birgit Laggner]

Yes, I have noticed that, but I don't know how to do that, especially at
defining my n (loop end point variable). I tried various versions (also
with execute), but without success.

Birgit.

On 17.12.2009 16:38, David William Bitner wrote:
> Birgit,
>
> The problem may be that you are creating a varchar variable for your
> schema name and then you are trying to use it in an instance that is
> expecting a database object. Anytime you are trying to insert
> variables as database objects, you need to construct your query as a
> string and use execute similar as to how you are creating your insert
> statement.
>
> David
>
> On Thu, Dec 17, 2009 at 9:27 AM, Birgit Laggner
> mailto:birgit.lagg...@vti.bund.de>> wrote:
>
> Dear list,
>
> I am trying to generalize a pl/pgsql function I have written (see
> below). I would like to define schema and table names, as well as
> certain column names, in the function call (as in the PostGIS function
> AddGeometryColumn) in order to use them to define schema and table
> names
> and everything else within the function queries.
>
> My problem is, that postgres doesn't recognize the defined variable
> names if I call them in a FROM clause or INSERT INTO.  This is the
> error
> message:
>
> ERROR:  Schema »schemaname« does not exist
> LINE 1: SELECT  count( $1 ) from schemaname.table_a
> ^
> QUERY:  SELECT  count( $1 ) from schemaname.table_a
> CONTEXT:  PL/pgSQL function "_laggner_b_pgintersection" line 16 at
> assignment
>
> I can't imagine that it should be impossible to use variable
> schema and
> table names in a plpgsql function. So, if anybody has suggestions, I
> would be quite happy.
>
> Thanks and regards,
>
> Birgit.
>
> My PostGIS version: 1.4.0-10.1
> My PostgreSQL version: 8.4.1-2.1
>
> My pl/pgsql function:
>
> CREATE OR REPLACE  FUNCTION _laggner_b_pgintersection(schemaname
> varchar(20), table_a varchar(50), a_id varchar(20), table_b
> varchar(50),
> b_id varchar(20), intersection varchar(60)) RETURNS void AS
> $BODY$
>
>  DECLARE
>  counter   integer;
>  recordset_object  RECORD;
>  i integer;
>  n integer;
>
>  BEGIN
>
>  counter := 0;
>  n := count(a_id) from schemaname.table_a;
>
> --1. Intersection:
>
>  FOR i in 1..n LOOP
>
>  RAISE NOTICE 'Beginn Intersection Tabelle 1, Polygon %', i;
>
>  FOR recordset_object IN
>
>  SELECT
>   a.a_id ,
>   b.b_id,
>   ST_intersection(a.the_geom, b.the_geom) AS the_geom
>  FROM schemaname.table_a a, schemaname.table_b b
>  WHERE a.a_id=i and
>st_intersects(a.the_geom, b.the_geom) and
>a.the_geom && b.the_geom
>
>  LOOP
>
>  execute
>  'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
> ''||b_id||'', the_geom) '||
>   'VALUES ( '||
>''||recordset_object||'.''||a_id||'', '||
>''||recordset_object||'.''||b_id||'', '||
>''||recordset_object||'.the_geom);';
> /*
> alternatively:
>  INSERT INTO schemaname.intersection (a_id, b_id, the_geom)
>   VALUES (
>recordset_object.a_id,
>recordset_object.b_id,
>recordset_object.the_geom);
> */
>  counter := counter + 1;
>
>  RAISE NOTICE 'Schreibe Intersection-Polygon %', counter ;
>
>  END LOOP;
>
>  counter := 0;
>
>  END LOOP;
>
>  END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
> ALTER FUNCTION _laggner_b_pgintersection(schemaname varchar(20),
> table_a
> varchar(50), a_id varchar(20), table_b varchar(50), b_id varchar(20),
> intersection varchar(60)) OWNER TO postgres;
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> <mailto:postgis-users@postgis.refractions.net>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> -- 
> 
> David William Bitner
>
>
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] plpgsql - problem using variable schema and table names

[Birgit Laggner]

Dear list,

I am trying to generalize a pl/pgsql function I have written (see
below). I would like to define schema and table names, as well as
certain column names, in the function call (as in the PostGIS function
AddGeometryColumn) in order to use them to define schema and table names
and everything else within the function queries.

My problem is, that postgres doesn't recognize the defined variable
names if I call them in a FROM clause or INSERT INTO.  This is the error
message:

ERROR:  Schema »schemaname« does not exist
LINE 1: SELECT  count( $1 ) from schemaname.table_a
 ^
QUERY:  SELECT  count( $1 ) from schemaname.table_a
CONTEXT:  PL/pgSQL function "_laggner_b_pgintersection" line 16 at
assignment

I can't imagine that it should be impossible to use variable schema and
table names in a plpgsql function. So, if anybody has suggestions, I
would be quite happy.

Thanks and regards,

Birgit.

My PostGIS version: 1.4.0-10.1
My PostgreSQL version: 8.4.1-2.1

My pl/pgsql function:

CREATE OR REPLACE  FUNCTION _laggner_b_pgintersection(schemaname
varchar(20), table_a varchar(50), a_id varchar(20), table_b varchar(50),
b_id varchar(20), intersection varchar(60)) RETURNS void AS
$BODY$

 DECLARE
  counter   integer;
  recordset_object  RECORD;
  i integer;
  n integer;

 BEGIN

  counter := 0;
  n := count(a_id) from schemaname.table_a;

--1. Intersection:

 FOR i in 1..n LOOP

 RAISE NOTICE 'Beginn Intersection Tabelle 1, Polygon %', i;
 
 FOR recordset_object IN
 
  SELECT
   a.a_id ,
   b.b_id,
   ST_intersection(a.the_geom, b.the_geom) AS the_geom
  FROM schemaname.table_a a, schemaname.table_b b
  WHERE a.a_id=i and
st_intersects(a.the_geom, b.the_geom) and
a.the_geom && b.the_geom

 LOOP

  execute
  'INSERT INTO ''||schemaname||''.''||intersection||'' (''||a_id||'',
''||b_id||'', the_geom) '||
   'VALUES ( '||
''||recordset_object||'.''||a_id||'', '||
''||recordset_object||'.''||b_id||'', '||
''||recordset_object||'.the_geom);';
/*
alternatively:
  INSERT INTO schemaname.intersection (a_id, b_id, the_geom)
   VALUES (
recordset_object.a_id,
recordset_object.b_id,
recordset_object.the_geom);
*/
 counter := counter + 1;

 RAISE NOTICE 'Schreibe Intersection-Polygon %', counter ;

 END LOOP;

 counter := 0;

 END LOOP;

 END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION _laggner_b_pgintersection(schemaname varchar(20), table_a
varchar(50), a_id varchar(20), table_b varchar(50), b_id varchar(20),
intersection varchar(60)) OWNER TO postgres;

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

Hi Regina, Kevin and Leo,

I just solved the problem. For testing purposes, I deleted the rows in
the data table which were causing problems. Then the function ran
without problems, but, unfortunately, the result was not as I expected.
After some research, I discovered that I used the data type "record" in
a wrong way. I thought this data type would store more than one row
which it doesn't... Now, I changed my function and it works properly,
also including the rows which were causing problems in the former version.

Thanks for your help!

Birgit.



On 15.12.2009 08:59, Paragon Corporation wrote:
> Birgit,
>
> Never gotten that error before, but I have had success at least with 8.4
> doing a vacuum analyze on the corrupted table.  When I get erros like
> page_header not found.
>
> You can try doing that and see if it makes a difference.  Though not sure if
> you can put that in a stored proc, since I've never tried. 
>
> Hope that helps,
> Regina 
>
> -Original Message-
> From: postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Birgit
> Laggner
> Sent: Monday, December 14, 2009 5:05 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] problem with plpgsql function - ERROR: could
> not open relation with OID XXX
>
> Hi Leo,
>
> with your TRUNCATE and ALTER SEQUENCE method, I still get an error at
> polygon 451, but a new one:
>
> ERROR: missing chunk number 0 for toast value 27366 in pg_toast_26963 SQL
> Status:XX000 Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 63
> at RAISE
>
> I think, this describes the problem better then the oid referencing error.
> As a result of a Google search, I got the impression that there might be a
> corrupted row (polygon 451) in the database table. Any suggestions how to
> solve the problem?
>
> Thanks,
>
> Birgit.
>
>
>
> Birgit Laggner wrote:
>   
>> Hi Kevin,
>>
>> to define the table name in question as a variable did not work, 
>> unfortunately. I get the same error message as before. Now, I will try 
>> the TRUNCATE and ALTER SEQUENCE method of Leo...
>>
>> Regards, Birgit.
>>
>>
>> Birgit Laggner wrote:
>> 
>>> Thanks, Kevin and Leo! I will try your suggestions today.
>>>
>>> Birgit.
>>>
>>> Kevin Neufeld wrote:
>>>   
>>>> Yeah, I agree.  What I've done to get around the caching problem 
>>>> that seems to work is to define all table names as variables at the 
>>>> top of the function.  All the sql statements used throughout the 
>>>> function then reference a variable instead of an actual table.  The 
>>>> planner can't cache the query plan since the query is adhoc ... no 
>>>> OID referencing problem.
>>>> -- Kevin
>>>>
>>>> Paragon Corporation wrote:
>>>> Birgit,
>>>>
>>>> I suspect as you alluded to that you are a victim of the dreaded 
>>>> cached plan and your OID issue is because the new table doesn't have 
>>>> the same OID as the old table. 8.4 is supposed to be smart enough to 
>>>> invalidate plans in these situations, thought maybe not.
>>>>
>>>> One possible work around is instead of creating and dropping the 
>>>> table, why don't you just TRUNCATE the table and reset the sequence
>>>>
>>>> So something like
>>>>
>>>>
>>>> TRUNCATE TABLE birgit.test_diff_dlm07_tmp; ALTER SEQUENCE 
>>>> birgit.test_diff_dlm07_tmp.gid RESTART WITH 1;
>>>>
>>>>
>>>> You could also use CREATE TEMP TABLE instead of CREATE TABLE.  I 
>>>> suspect temp table oids may not be cached.
>>>>
>>>> Leo
>>>>
>>>>
>>>> -Original Message-
>>>> From: postgis-users-boun...@postgis.refractions.net
>>>> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of 
>>>> Birgit Laggner
>>>> Sent: Thursday, December 03, 2009 12:26 PM
>>>> To: PostGIS Users Discussion
>>>> Subject: [postgis-users] problem with plpgsql function - ERROR: 
>>>> could not open relation with OID XXX
>>>>
>>>> Dear list,
>>>>
>>>> I have written a pl/pgsql function (see below) for st_difference 
>>>> which in short should sequentially scan a geometric table (a) if 
>>>> there are intersections with geometric table (b) and if there are, 
>>>> it 

Re: [postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

Hi Leo,

with your TRUNCATE and ALTER SEQUENCE method, I still get an error at
polygon 451, but a new one:

ERROR: missing chunk number 0 for toast value 27366 in pg_toast_26963
SQL Status:XX000
Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 63 at RAISE

I think, this describes the problem better then the oid referencing
error. As a result of a Google search, I got the impression that there
might be a corrupted row (polygon 451) in the database table. Any
suggestions how to solve the problem?

Thanks,

Birgit.



Birgit Laggner wrote:
>Hi Kevin,
>
>to define the table name in question as a variable did not work,
>unfortunately. I get the same error message as before. Now, I will try
>the TRUNCATE and ALTER SEQUENCE method of Leo...
>
>Regards, Birgit.
>
>
>Birgit Laggner wrote:
>>Thanks, Kevin and Leo! I will try your suggestions today.
>>
>>Birgit.
>>
>>Kevin Neufeld wrote:
>>> Yeah, I agree.  What I've done to get around the caching problem that
>>> seems to work is to define all table names as variables at the top of
>>> the function.  All the sql statements used throughout the function
>>> then reference a variable instead of an actual table.  The planner
>>> can't cache the query plan since the query is adhoc ... no OID
>>> referencing problem.
>>> -- Kevin
>>>
>>> Paragon Corporation wrote:
>>> Birgit,
>>>
>>> I suspect as you alluded to that you are a victim of the dreaded
>>> cached plan
>>> and your OID issue is because the new table doesn't have the same OID
>>> as the
>>> old table. 8.4 is supposed to be smart enough to invalidate plans in
>>> these
>>> situations, thought maybe not.
>>>
>>> One possible work around is instead of creating and dropping the
>>> table, why
>>> don't you just TRUNCATE the table and reset the sequence
>>>
>>> So something like
>>>
>>>
>>> TRUNCATE TABLE birgit.test_diff_dlm07_tmp;
>>> ALTER SEQUENCE birgit.test_diff_dlm07_tmp.gid RESTART WITH 1;
>>>
>>>
>>> You could also use CREATE TEMP TABLE instead of CREATE TABLE.  I suspect
>>> temp table oids may not be cached.
>>>
>>> Leo
>>>
>>>
>>> -Original Message-
>>> From: postgis-users-boun...@postgis.refractions.net
>>> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
>>> Birgit
>>> Laggner
>>> Sent: Thursday, December 03, 2009 12:26 PM
>>> To: PostGIS Users Discussion
>>> Subject: [postgis-users] problem with plpgsql function - ERROR: could
>>> not
>>> open relation with OID XXX
>>>
>>> Dear list,
>>>
>>> I have written a pl/pgsql function (see below) for st_difference
>>> which in
>>> short should sequentially scan a geometric table (a) if there are
>>> intersections with geometric table (b) and if there are, it writes the
>>> intersecting polygons of table (b) into an extra table and then
>>> executes the
>>> st_difference for the actual polygon of table (a) and all polygons of
>>> table
>>> (b ) written in the extra table as a sequence always using the
>>> product of
>>> the last difference as the input (instead of the table (a)
>>> polygon) of the next difference. I hope everybody understands my way of
>>> thinking ;-)
>>>
>>> My problem is now, that at polygon 451 of table (a), the function
>>> stops with
>>> the following error message:
>>>
>>> ERROR: could not open relation with OID 25736 SQL Status:XX000
>>> Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE
>>>
>>> Strange is, that the function did run successfully for more than 100
>>> difference-loops. In an older PostGres version (8.1...), I have had a
>>> similar problem, but then always in the 2nd loop, because of the
>>> cashing-problem of the query planner.
>>>
>>> This are the PostGIS/PostgreSQL versions I am using:
>>> PostgreSQL: 8.4.1-2.1
>>> PostGIS: 1.4.0-10.1
>>>
>>> Here, the last few message rows of the running function, perhaps this
>>> helps
>>> with understanding the problem (sorry because it's partly in German,
>>> I hope
>>> it doesn't matter):
>>>
>>> NOTICE:  Beginn Difference für dlm07-Polygon 450
>>> NOTICE:  Anzahl Intersection-Polygo

Re: [postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

Hi Kevin,

to define the table name in question as a variable did not work,
unfortunately. I get the same error message as before. Now, I will try
the TRUNCATE and ALTER SEQUENCE method of Leo...

Regards, Birgit.


Birgit Laggner wrote:
>Thanks, Kevin and Leo! I will try your suggestions today.
>
>Birgit.
>
>Kevin Neufeld schrieb:
>> Yeah, I agree.  What I've done to get around the caching problem that
>> seems to work is to define all table names as variables at the top of
>> the function.  All the sql statements used throughout the function
>> then reference a variable instead of an actual table.  The planner
>> can't cache the query plan since the query is adhoc ... no OID
>> referencing problem.
>> -- Kevin
>>
>> Paragon Corporation wrote:
>> Birgit,
>>
>> I suspect as you alluded to that you are a victim of the dreaded
>> cached plan
>> and your OID issue is because the new table doesn't have the same OID
>> as the
>> old table. 8.4 is supposed to be smart enough to invalidate plans in
>> these
>> situations, thought maybe not.
>>
>> One possible work around is instead of creating and dropping the
>> table, why
>> don't you just TRUNCATE the table and reset the sequence
>>
>> So something like
>>
>>
>> TRUNCATE TABLE birgit.test_diff_dlm07_tmp;
>> ALTER SEQUENCE birgit.test_diff_dlm07_tmp.gid RESTART WITH 1;
>>
>>
>> You could also use CREATE TEMP TABLE instead of CREATE TABLE.  I suspect
>> temp table oids may not be cached.
>>
>> Leo
>>
>>
>> -Original Message-
>> From: postgis-users-boun...@postgis.refractions.net
>> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
>> Birgit
>> Laggner
>> Sent: Thursday, December 03, 2009 12:26 PM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] problem with plpgsql function - ERROR: could
>> not
>> open relation with OID XXX
>>
>> Dear list,
>>
>> I have written a pl/pgsql function (see below) for st_difference
>> which in
>> short should sequentially scan a geometric table (a) if there are
>> intersections with geometric table (b) and if there are, it writes the
>> intersecting polygons of table (b) into an extra table and then
>> executes the
>> st_difference for the actual polygon of table (a) and all polygons of
>> table
>> (b ) written in the extra table as a sequence always using the
>> product of
>> the last difference as the input (instead of the table (a)
>> polygon) of the next difference. I hope everybody understands my way of
>> thinking ;-)
>>
>> My problem is now, that at polygon 451 of table (a), the function
>> stops with
>> the following error message:
>>
>> ERROR: could not open relation with OID 25736 SQL Status:XX000
>> Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE
>>
>> Strange is, that the function did run successfully for more than 100
>> difference-loops. In an older PostGres version (8.1...), I have had a
>> similar problem, but then always in the 2nd loop, because of the
>> cashing-problem of the query planner.
>>
>> This are the PostGIS/PostgreSQL versions I am using:
>> PostGIS: 8.4.1-2.1
>> PostgreSQL: 1.4.0-10.1
>>
>> Here, the last few message rows of the running function, perhaps this
>> helps
>> with understanding the problem (sorry because it's partly in German,
>> I hope
>> it doesn't matter):
>>
>> NOTICE:  Beginn Difference für dlm07-Polygon 450
>> NOTICE:  Anzahl Intersection-Polygone: 1
>> NOTICE:  CREATE TABLE erstellt implizit eine Sequenz
>> »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte
>> »test_diff_dlm07_tmp.gid«
>> CONTEXT:  SQL-Anweisung »create table birgit.test_diff_dlm07_tmp (gid
>> serial, inv07_id integer, the_geom geometry);« PL/pgSQL function
>> "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung
>> NOTICE:  recordset_object2a:
>> (309108,010320EB7A010022009B8A50B33D3D4A410CFC87519141564121
>>
>> B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A
>>
>> 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E943870
>>
>> 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D792
>>
>> 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142
>>
>> 690C973A4156419B31B5B3893C4A41BA142C323341564

Re: [postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

Thanks, Kevin and Leo! I will try your suggestions today.

Birgit.

Kevin Neufeld schrieb:
> Yeah, I agree.  What I've done to get around the caching problem that
> seems to work is to define all table names as variables at the top of
> the function.  All the sql statements used throughout the function
> then reference a variable instead of an actual table.  The planner
> can't cache the query plan since the query is adhoc ... no OID
> referencing problem.
> -- Kevin
>
> Paragon Corporation wrote:
>> Birgit,
>>
>> I suspect as you alluded to that you are a victim of the dreaded
>> cached plan
>> and your OID issue is because the new table doesn't have the same OID
>> as the
>> old table. 8.4 is supposed to be smart enough to invalidate plans in
>> these
>> situations, thought maybe not.
>>
>> One possible work around is instead of creating and dropping the
>> table, why
>> don't you just TRUNCATE the table and reset the sequence
>>
>> So something like
>>
>>
>> TRUNCATE TABLE birgit.test_diff_dlm07_tmp;
>> ALTER SEQUENCE birgit.test_diff_dlm07_tmp.gid RESTART WITH 1;
>>
>>
>> You could also use CREATE TEMP TABLE instead of CREATE TABLE.  I suspect
>> temp table oids may not be cached.
>>
>> Leo
>>
>>
>> -Original Message-
>> From: postgis-users-boun...@postgis.refractions.net
>> [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
>> Birgit
>> Laggner
>> Sent: Thursday, December 03, 2009 12:26 PM
>> To: PostGIS Users Discussion
>> Subject: [postgis-users] problem with plpgsql function - ERROR: could
>> not
>> open relation with OID XXX
>>
>> Dear list,
>>
>> I have written a pl/pgsql function (see below) for st_difference
>> which in
>> short should sequentially scan a geometric table (a) if there are
>> intersections with geometric table (b) and if there are, it writes the
>> intersecting polygons of table (b) into an extra table and then
>> executes the
>> st_difference for the actual polygon of table (a) and all polygons of
>> table
>> (b ) written in the extra table as a sequence always using the
>> product of
>> the last difference as the input (instead of the table (a)
>> polygon) of the next difference. I hope everybody understands my way of
>> thinking ;-)
>>
>> My problem is now, that at polygon 451 of table (a), the function
>> stops with
>> the following error message:
>>
>> ERROR: could not open relation with OID 25736 SQL Status:XX000
>> Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE
>>
>> Strange is, that the function did run successfully for more than 100
>> difference-loops. In an older PostGres version (8.1...), I have had a
>> similar problem, but then always in the 2nd loop, because of the
>> cashing-problem of the query planner.
>>
>> This are the PostGIS/PostgreSQL versions I am using:
>> PostGIS: 8.4.1-2.1
>> PostgreSQL: 1.4.0-10.1
>>
>> Here, the last few message rows of the running function, perhaps this
>> helps
>> with understanding the problem (sorry because it's partly in German,
>> I hope
>> it doesn't matter):
>>
>> NOTICE:  Beginn Difference für dlm07-Polygon 450
>> NOTICE:  Anzahl Intersection-Polygone: 1
>> NOTICE:  CREATE TABLE erstellt implizit eine Sequenz
>> »test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte
>> »test_diff_dlm07_tmp.gid«
>> CONTEXT:  SQL-Anweisung »create table birgit.test_diff_dlm07_tmp (gid
>> serial, inv07_id integer, the_geom geometry);« PL/pgSQL function
>> "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung
>> NOTICE:  recordset_object2a:
>> (309108,010320EB7A010022009B8A50B33D3D4A410CFC87519141564121
>>
>> B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A
>>
>> 41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E943870
>>
>> 4156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D792
>>
>> 9CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142
>>
>> 690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A4170493851304156
>>
>> 41D1ABC0367F3C4A412B4156415FC001E8EB3B4A412B41564163BDF058E9
>>
>> 3B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015
>>
>> 233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C494156

[postgis-users] problem with plpgsql function - ERROR: could not open relation with OID XXX

[Birgit Laggner]

Dear list,

I have written a pl/pgsql function (see below) for st_difference which
in short should sequentially scan a geometric table (a) if there are
intersections with geometric table (b) and if there are, it writes the
intersecting polygons of table (b) into an extra table and then executes
the st_difference for the actual polygon of table (a) and all polygons
of table (b ) written in the extra table as a sequence always using the
product of the last difference as the input (instead of the table (a)
polygon) of the next difference. I hope everybody understands my way of
thinking ;-)

My problem is now, that at polygon 451 of table (a), the function stops
with the following error message:

ERROR: could not open relation with OID 25736
SQL Status:XX000
Kontext:PL/pgSQL function "_laggner_b_pgdifference_a" line 67 at RAISE

Strange is, that the function did run successfully for more than 100
difference-loops. In an older PostGres version (8.1...), I have had a
similar problem, but then always in the 2nd loop, because of the
cashing-problem of the query planner.

This are the PostGIS/PostgreSQL versions I am using:
PostGIS: 8.4.1-2.1
PostgreSQL: 1.4.0-10.1

Here, the last few message rows of the running function, perhaps this
helps with understanding the problem (sorry because it's partly in
German, I hope it doesn't matter):

NOTICE:  Beginn Difference für dlm07-Polygon 450
NOTICE:  Anzahl Intersection-Polygone: 1
NOTICE:  CREATE TABLE erstellt implizit eine Sequenz
»test_diff_dlm07_tmp_gid_seq« für die »serial«-Spalte
»test_diff_dlm07_tmp.gid«
CONTEXT:  SQL-Anweisung »create table birgit.test_diff_dlm07_tmp (gid
serial, inv07_id integer, the_geom geometry);«
PL/pgSQL function "_laggner_b_pgdifference_a" line 39 at EXECUTE-Anweisung
NOTICE:  recordset_object2a:
(309108,010320EB7A010022009B8A50B33D3D4A410CFC87519141564121B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E9438704156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D7929CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A417049385130415641D1ABC0367F3C4A412B4156415FC001E8EB3B4A412B41564163BDF058E93B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD9833664156419FF0E035993C4A4127C0195969415641E9615636B93C4A41F74650597441564112500996C43C4A41609FC0987841564123E75FD9CE3C4A418FDC793F9141564183752A27DB3C4A418B99EA269041564192DE2C6CFB3C4A41C198D3C29041564174C52E98113D4A41BB824C8F91415641579F17352D3D4A41AF447FC5914156419B8A50B33D3D4A410CFC875191415641,1)
NOTICE:  recordset_object1:
(450,010320EB7A01002200CC72F1149A3C4A412B415641295C8F229A3C4A4185EB51582B4156413E0AD7A3983C4A412B415641351D7C68833C4A412B415641F6285C2F7E3C4A41001031415641B81E856B913C4A41A4703D6A354156413E0AD723AD3C4A41AE47E17A3B415641B81E850BAE3C4A4114AE47E13E415641E17A144EAF3C4A417B14AEE74341564152B81EA5B03C4A410AD7A3B04741564114AE4721BB3C4A41EC51B82E474156417B14AE67CC3C4A41AE47E17A46415641B81E852BD83C4A411F85EB01464156416646D73C4A4185EB51B8454156413E0AD783D63C4A414541564114AE4761D53C4A41B81E85DB44415641295C8F02D53C4A418FC2F548444156413E0AD703D53C4A4166C6434156419A79D53C4A41AE47E1CA424156410AD7A330D63C4A41713D0AA741415641A4703D8AD73C4A41B81E859B40415641A4703DEAD93C4A41E17A14CE3E415641713D0A17DB3C4A41B81E85DB3D415641CD8CDC3C4A41F6285CAF3C415641E17A148EDF3C4A41AE47E10A3B4156419A59E13C4A410AD7A3103A4156413E0AD7E3E23C4A4148E17AC438415641A4703D0AE43C4A41AE47E13A38415641A4703DCAEB3C4A4114AE4721384156410060EC3C4A413E0AD7D33241564114AE47E1E03C4A41D7A370AD324156419A79E33C4A417B14AE772E415641882F554CE43C4A412B415641CC72F1149A3C4A412B415641)
NOTICE:  recordset_object2a:
(309108,010320EB7A010022009B8A50B33D3D4A410CFC87519141564121B29CCB563D4A4106E2E205904156415229B1B2773D4A41BE9861008E415641D117B6AD773D4A41B9A76ADF6F4156410BF8A3EF3F3D4A41BD0E943870415641AFF9CD0B1D3D4A41BD0E9438704156417CB35079F93C4A419BD797C96F41564178CEEED7F63C4A41E6DDA2F050415641D2D7929CF53C4A41DF1DF01D46415641D5BD901CAE3C4A41F8BCF27D464156415E242F79AD3C4A4142690C973A4156419B31B5B3893C4A41BA142C323341564105B615097B3C4A417049385130415641D1ABC0367F3C4A412B4156415FC001E8EB3B4A412B41564163BDF058E93B4A415E78DD7F2D4156417B1DF30FE43B4A4108E03B8E324156412AD0C960063C4A419F2015233A41564114F5B712333C4A41DA07856543415641A34C690B4D3C4A41F4AF672C49415641EE6CACD25B3C4A41A76FFFEE4C4156412B1572B3653C4A4194013F074F4156417A6F642C703C4A41ED905744544156416FED9E4A833C4A417BFF4EB75F41564133189B64913C4A4111FD9833664156419FF0E035993C4A4127C0195969415641E9615636B93C

Re: [postgis-users] Help! Calculating distance

[Birgit Laggner]

Hi Zia,

my guess would be:

select
points.point_ID,
min(st_distance(highway.the_geom, points.the_geom)),
min(st_distance(stream.the_geom, points.the_geom)),
min(st_distance(mining_side.the_geom, points.the_geom))
from points, highway, stream, mining_side
group by points.point_ID;

but this might take some time - perhaps someone smarter than me has a
better idea ...

Regards,

Birgit.

Zia Ahmed schrieb:
> Hi,
>
> I have point file (> 45, 000 points)  containing information of soil
> properties, I want  to calculate euclidean distance from nearest
> highway  (line-file), stream (line-file) and mining side (point and
> polygon file). Can I do this  in PostGIS?
>
> Zia
> ___
> postgis-users mailing list
> postgis-users@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

-- 
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5299
E-Mail: birgit.lagg...@vti.bund.de
Internet: www.vti.bund.de

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Re: Memory problem with ST_Within

[Birgit Laggner]

Hi Shane,

this is the function, our admin wrote to work through my query, row by row:

CREATE OR REPLACE FUNCTION dh_test()
RETURNS void AS
$BODY$
declare
i integer;
begin
for i in 1..15099748 loop
execute 'insert into bfn.ni_stat_hoehendaten (polygon_id, hoehe_count, 
hoehe_min, hoehe_max, hoehe_avg,' ||
' hoehe_stdev,neig_count, neig_min, neig_max, neig_avg, expos_count, 
expos_min, expos_max, expos_avg, expos_stdev, the_geom)' ||
' select polygon_id, count(hoehe) as hoehe_count, min(hoehe) as 
hoehe_min, max(hoehe) as hoehe_max,' ||
' avg(hoehe) as hoehe_avg, stddev_samp(hoehe) as hoehe_stdev, 
count(neigung) as neig_count, min(neigung) as neig_min,' ||
' max(neigung) as neig_max, avg(neigung) as neig_avg, count(exposition) 
as expos_count,' ||
' min(exposition) as expos_min, max(exposition) as expos_max, 
avg(exposition) as expos_avg, stddev_samp(exposition) as expos_stdev,' ||

' p.the_geom' ||
' from bfn.ni_hoehendaten h,bfn.ni_polygone2 p' ||
' where st_within(h.the_geom,p.the_geom) and p.polygon_id=' || i || ' 
group by p.polygon_id, p.the_geom;';

end loop;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dh_test() OWNER TO postgres;

Precondition: pl/pgsql has to be installed.

If you need any explanation to that, please ask.

Regards,

Birgit.


Shane Butler schrieb:

Dear Birgit and List,

I am getting an out of memory error (see below) when doing a big query
that uses ST_Within(). This was described by Birgit on this list back
in August.  Is there a solution?

Birgit, can you please explain the work around you mentioned on Aug 14:
http://postgis.refractions.net/pipermail/postgis-users/2008-August/020984.html

Any help would be greatly appreciated!

Kind Regards,
Shane

---

Some details of the error:
"ERROR:  out of memory
DETAIL:  Failed on request of size 32.

** Error **

ERROR: out of memory
SQL state: 53200
Detail: Failed on request of size 32."


My system info:
"POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec
2007" USE_STATS"

  

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

ahhh! thank you very much. I thought, I've always made a validity check 
but this clearly proves the contrary :-)


ok, this is something, I can change - very good.

Thank you, Regina!

Regards,

Birgit.


Obe, Regina schrieb:
Ah I see the problem now.  

SELECT ST_AsText(ST_Centroid(ST_GeomFromTExt('POLYGON((3713601.737 
5966193.371,3713601.737 5966193.371,3713601.737

5966193.371,3713601.737
5966193.371))')))

Returns - "GEOMETRYCOLLECTION EMPTY"

The problem is that polygon is invalid.

If I do this
SELECT ST_IsValid(ST_GeomFromTExt('POLYGON((3713601.737 
5966193.371,3713601.737 5966193.371,3713601.737

5966193.371,3713601.737 5966193.371))'))

I get 
f  and a notice that says

NOTICE:  Too few points in geometry component at or near point
3.7136e+06 5.96619e+06

So I guess the moral of the story - change your query to include a 


WHERE ST_IsValid(abdump.newgeom)


Hope that helps,
Regina
 


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Wednesday, October 15, 2008 9:56 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after
ST_Centroid(ST_Dump)

Hi Regina,

this is the result, I get from the query you sent me:

gid  geometrytype_centroid  astxtrep
integer  text   text

-
119810   "GEOMETRYCOLLECTION"   "POLYGON((3713601.737 
5966193.371,3713601.737 5966193.371,3713601.737

5966193.371,3713601.737 5966193.371))"

Thanks and regards,

Birgit.


Obe, Regina schrieb:
  

Birgit,
Ah that is weird - I've never seen it return a geometry collection
before.  So what is the ST_AsText/ST_AsBinary of the first.

e.g.
SELECT
  abdump.gid,
  geometrytype(ST_Centroid(abdump.newgeom)) As geometrytype_centroid,
  ST_AsText(abdump.newgeom) as astxtrep
FROM
 (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As 
newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom

  FROM  bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
  WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

 ) As abdump
WHERE abdump.gid = 119810
GROUP BY (ST_Centroid(abdump.newgeom)), 
ST_Area(ST_Centroid(abdump.newgeom)), 
ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom;



By the way the result you sent doesn't match the query - its missing a
field and the labels don't match the query.
  

Yes, that's true - I first made the query with the geom 
(abdump.newgeom), but then deleted this from the query because the geom 
would have needed such a wide column in the result table. And I forgot 
to update this in my e-mail...


  

Hope that helps,
Regina

 


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Wednesday, October 15, 2008 8:45 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after
ST_Centroid(ST_Dump)

Hi Regina,

yes, that's true. The queries you took for testing work  for me, too. 
But if I try to run:


SELECT
  ST_X(st_centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) AS x_centroid,
  ST_Y(st_centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) AS y_centroid,

  ST_Area((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) AS
area,
  ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom,

b.the_geom))).geom) 
  

AS perimeter
FROM bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
WHERE ST_OVERLAPS(a.the_geom,b.the_geom)=true AND 
INTERSECTS(a.the_geom,b.the_geom)=true
GROUP BY ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), 
ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), ST_Area((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom,



  

b.the_geom))).geom), a.the_geom, b.the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc;

I still get: error: Argument to X() must be a point.

The same happens when I formulate the query like you suggested in the 
beginning:


SELECT
  abdump.gid,
  ST_X(ST_Centroid(abdump.newgeom)) As x_centroid,
  ST_Y(ST_Centroid(abdump.newgeom)) As y_centroid,
  ST_Area(ST_Centroid(abdump.newgeom)) As area,
  ST_Perimeter(ST_Centroid(abdump.newgeom)) as perimeter,
  abdump.newgeom
FROM
 (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As 
newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom

  FROM  bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
  WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

 ) As abdump
GROUP BY ST_X(ST_Centroid(abdump.newgeom)), 
ST_Y(ST_Centroid(abdump.newgeom)),


ST_Area(ST_Cent

Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

Hi Regina,

this is the result, I get from the query you sent me:

gid  geometrytype_centroid  astxtrep
integer  text   text
-
119810   "GEOMETRYCOLLECTION"   "POLYGON((3713601.737 
5966193.371,3713601.737 5966193.371,3713601.737

   5966193.371,3713601.737 5966193.371))"

Thanks and regards,

Birgit.


Obe, Regina schrieb:

Birgit,
Ah that is weird - I've never seen it return a geometry collection
before.  So what is the ST_AsText/ST_AsBinary of the first.

e.g.
SELECT
  abdump.gid,
  geometrytype(ST_Centroid(abdump.newgeom)) As geometrytype_centroid,
  ST_AsText(abdump.newgeom) as astxtrep
FROM
 (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As 
newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom

  FROM  bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
  WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

 ) As abdump
WHERE abdump.gid = 119810
GROUP BY (ST_Centroid(abdump.newgeom)), 
ST_Area(ST_Centroid(abdump.newgeom)), 
ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom;



By the way the result you sent doesn't match the query - its missing a
field and the labels don't match the query.
  
Yes, that's true - I first made the query with the geom 
(abdump.newgeom), but then deleted this from the query because the geom 
would have needed such a wide column in the result table. And I forgot 
to update this in my e-mail...




Hope that helps,
Regina

 


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Wednesday, October 15, 2008 8:45 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after
ST_Centroid(ST_Dump)

Hi Regina,

yes, that's true. The queries you took for testing work  for me, too. 
But if I try to run:


SELECT
  ST_X(st_centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) AS x_centroid,
  ST_Y(st_centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) AS y_centroid,

  ST_Area((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) AS
area,
  ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) 
AS perimeter

FROM bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
WHERE ST_OVERLAPS(a.the_geom,b.the_geom)=true AND 
INTERSECTS(a.the_geom,b.the_geom)=true
GROUP BY ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), 
ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), ST_Area((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), a.the_geom, b.the_geom

ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc;

I still get: error: Argument to X() must be a point.

The same happens when I formulate the query like you suggested in the 
beginning:


SELECT
  abdump.gid,
  ST_X(ST_Centroid(abdump.newgeom)) As x_centroid,
  ST_Y(ST_Centroid(abdump.newgeom)) As y_centroid,
  ST_Area(ST_Centroid(abdump.newgeom)) As area,
  ST_Perimeter(ST_Centroid(abdump.newgeom)) as perimeter,
  abdump.newgeom
FROM
 (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As 
newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom

  FROM  bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
  WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

 ) As abdump
GROUP BY ST_X(ST_Centroid(abdump.newgeom)), 
ST_Y(ST_Centroid(abdump.newgeom)), ST_Area(ST_Centroid(abdump.newgeom)),


ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom
--a_the_geom, b_the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, 
abdump.gid asc;


Meanwhile, I am a bit confused, because I see no logic in what
happens...

Just to explain what I thougt, the problem might be:

When I run the following query:
SELECT
  abdump.gid,
  geometrytype(ST_Centroid(abdump.newgeom)) As geometrytype_centroid,
  abdump.newgeom
FROM
 (SELECT (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom As 
newgeom, a.gid, a.the_geom as a_the_geom, b.the_geom as b_the_geom

  FROM  bfn.test_mv_dlm07_dump a, bfn.test_mv_dlm07_dump b
  WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

 ) As abdump
GROUP BY (ST_Centroid(abdump.newgeom)), 
ST_Area(ST_Centroid(abdump.newgeom)), 
ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.newgeom;


I get this in return:

gid  geometrytype
integer text

119810  "GEOMETRYCOLLECTION"
120217  "GEOMETRYCOLLECTION"
135837  "GEOMETRYCOLLECTION"
135837  "GEOMETRYCOLLECTION"
101408  "GEOMETRYCOLLECTION"
254497  "GEOMETRYCOLLECTION"
125720  "GEOMETRYC

Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

"POINT"
135829  "POINT"
135850  "POINT"
125742  "POINT"
125742  "POINT"
135850  "POINT"
125731  "POINT"
135829  "POINT"
125731  "POINT"
135850  "POINT"
244155  "GEOMETRYCOLLECTION"
242621  "GEOMETRYCOLLECTION"
125731  "POINT"
135829  "POINT"
125742  "POINT"
135850  "POINT"
135837  "POINT"
125720  "POINT"
125720  "POINT"
135837  "POINT"
245042  "GEOMETRYCOLLECTION"
253750  "GEOMETRYCOLLECTION"
125720  "POINT"
135837  "POINT"
125720  "POINT"
135850  "POINT"
375311  "GEOMETRYCOLLECTION"
405587  "GEOMETRYCOLLECTION"
376289  "GEOMETRYCOLLECTION"
254671  "GEOMETRYCOLLECTION"
135837  "POINT"
125720  "POINT"
125720  "POINT"
404117  "GEOMETRYCOLLECTION"
135837  "POINT"
386583  "GEOMETRYCOLLECTION"
386466  "GEOMETRYCOLLECTION"
125720  "POINT"
135837  "POINT"
125720  "POINT"
135837  "POINT"
125720  "POINT"
135837  "POINT"
125720  "POINT"
135837  "POINT"
426566  "GEOMETRYCOLLECTION"

This is why I think, the ST_Centroid produces a geometrycollection.

Thanks again!

Regards,

Birgit.

Obe, Regina schrieb:

Birgit,

I just loaded up this data set you posted and tried doing

SELECT ST_X(ST_Centroid(the_geom))
FROM test_mv_dlm07_dump;

Works perfectly fine for me.   Even if you pass a geometry collection to 
ST_Centroid, it would always return a point.

To demonstrate

SELECT ST_GeometryType(ST_Centroid(ST_Collect(the_geom))), 
ST_AsText(ST_Centroid(ST_Collect(the_geom))) as full_cent
FROM test_mv_dlm07_dump;

Gives me this:
"ST_Point";"POINT(3728516.33771743 5964986.31004069)"


Are you saying the sample you posted when you do the above doesn't work for 
you?  If so, then there appears to be something wrong with your install.

What does 


SELECT postgis_full_version();

return for you?
  
"POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" 
USE_STATS"

Thanks,
Regina

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Birgit Laggner
Sent: Wednesday, October 15, 2008 5:03 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Hi Regina,

thanks for your response! I tried the query you suggested and still get 
the same result (error: Argument to X() must be a point), which is 
totally reasonable, regarding that the result of the ST_Centroid is 
still a GeometryCollection. My guess was, that this is caused by the 
fact that ST_Dump returns not only POLYGONs but also LINESTRINGs and 
POINTs. I thought, it could be that ST_Centroid has difficulties 
returning a centroid for points and linestrings. So, what I did so far, 
was to split the query into one part, with which I just find all 
overlapping geometries without grouping or anything else, and one part, 
which does the grouping including calculating of the centroid and 
everything else. And, between these two parts, I just delete all 
geometries that are LINESTRINGs or POINTs (I don't need them, I only 
need the polygons). At least, these queries run - I don't know yet if 
the result is correct...
But, as I saw now, when I selected the data example you asked for (see 
attachement), that the GeometrieCollections don't occur at the 
linestring or point geometries as I expected. There seems to be some 
other problem, I don't understand...


Here my new (working) query:

CREATE TABLE bfn.mv_dlm07_olvg
(
  gid serial NOT NULL,
  gid_mv_dlm07 integer
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_olvg','the_geom','31467','MULTIPOLYGON',2);


ALTER TABLE bfn.mv_dlm07_olvg DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_olvg (
  gid_mv_dlm07,
  the_geom)
SELECT
  a.gid,
  (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom AS the_geom
FROM bfn.mv_dlm07_orig a, bfn.mv_dlm07_orig b
WHERE ST_OVERLAPS(a.the_geom,b.the_geom)=true AND 
INTERSECTS(a.the_geom,b.the_geom)=true;


DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='LINESTRING';
DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='POINT';

CREATE TABLE bfn.mv_dlm07_ol
(
  gid serial NOT NULL,
  gid_mv_dlm07 integer,
  x_centroid numeric,
  y_centroid numeric,
  area numeric,
  perimeter numeric
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_ol','the_geom','31467','MULTIPOLYGON',2);


ALTER TABLE bfn.mv_dlm07_ol DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_ol (
  gid_mv_dlm07,
  x_centroid,

Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

Hi Chris,

thanks for your response. Normally, I shouldn't have multiple geometries 
because of the ST_Dump and I don't have any when I ask for 
Geometrytype() of the ST_Dump result. But still, splitting the GROUP BY 
from the INTERSECTION was seemingly a good idea (see my e-mail to Regina).


Regards,

Birgit.

Chris Hermansen schrieb:

Is it possible the GROUP BY produces geometry collections when there are
multiple geometries returned?  That seems at least plausible to me.

Maybe if you try to look at what's returned with and without the GROUP
BY in the cases where multiple geometries are returned (HAVING would
help there).

Obe, Regina wrote:
  

Birgit,

So you are saying ST_Centroid sometimes returns geometry collections.
Hmm I don't see how that is possible unless there is bug somewhere.

Can you provide an example of that.  Regarding the below query - it
would be a bit easier to follow if you did a subselect first.

Something like

 SELECT
  abdump.gid,
  abdump.objart_07,
 abdump.cat_07,
ST_X(ST_Centroid(abdump.newgeom)) As x_centroid,
ST_Y(ST_Centroid(abdump.newgeom)) As y_centroid
ST_Area(ST_Centroid(abdump.newgeom)) As area,
ST_Perimeter(ST_Centroid(abdump.newgeom)) as perimeter
FROM (SELECT (ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom As newgeom, a.gid, a.objart_07, a.cat_07, a.the_geom

as a_the_geom, b.the_geom as b_the_geom
FROM 
 bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

) As abdump
GROUP BY ST_X(ST_Centroid(abdump.newgeom)), 
ST_Y(ST_Centroid(abdump.newgeom)), ST_Area(ST_Centroid(abdump.newgeom)),


ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.objart_07,
abdump.cat_07, a_the_geom, b_the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid
asc;

Hope that helps,
Regina

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Tuesday, October 14, 2008 6:56 AM
To: PostGIS Users Discussion
Subject: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Dear list,

I have a SELECT statement in which I try to group by the polygons 
resulting from a self intersection. After I realized that I can't group 
by the_geom because this would group polygons which are only similar but


not equal, I decided to group by the area, the perimeter and the x- and 
y-coordinates of the centroid of every polygon. The SELECT statement I 
wrote, worked so far for some test datasets and showed seemingly 
reasonable results. But for at least one of my datasets, the statement 
does not work, because the ST_Centroid produces some GeometryCollections


instead of POINTS. What could cause that problem and how can I solve
it???

This is my statement:

SELECT
  a.gid,
  a.objart_07,
  a.cat_07,
  ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) as x_centroid,
  ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) as y_centroid,

  ST_Area((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) as
area,
  ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) 
as perimeter,

  (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom as the_geom
FROM bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true
GROUP BY ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), 
ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), ST_Area((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), a.gid, a.objart_07, a.cat_07, a.the_geom,

b.the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid
asc;

The ST_Dump results in POLYGONs, LINESTRINGs and POINTs.

Thank you for any help!

Regards,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
  




  


--
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-3

Re: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

Hi Regina,

thanks for your response! I tried the query you suggested and still get 
the same result (error: Argument to X() must be a point), which is 
totally reasonable, regarding that the result of the ST_Centroid is 
still a GeometryCollection. My guess was, that this is caused by the 
fact that ST_Dump returns not only POLYGONs but also LINESTRINGs and 
POINTs. I thought, it could be that ST_Centroid has difficulties 
returning a centroid for points and linestrings. So, what I did so far, 
was to split the query into one part, with which I just find all 
overlapping geometries without grouping or anything else, and one part, 
which does the grouping including calculating of the centroid and 
everything else. And, between these two parts, I just delete all 
geometries that are LINESTRINGs or POINTs (I don't need them, I only 
need the polygons). At least, these queries run - I don't know yet if 
the result is correct...
But, as I saw now, when I selected the data example you asked for (see 
attachement), that the GeometrieCollections don't occur at the 
linestring or point geometries as I expected. There seems to be some 
other problem, I don't understand...


Here my new (working) query:

CREATE TABLE bfn.mv_dlm07_olvg
(
 gid serial NOT NULL,
 gid_mv_dlm07 integer
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_olvg','the_geom','31467','MULTIPOLYGON',2);


ALTER TABLE bfn.mv_dlm07_olvg DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_olvg (
 gid_mv_dlm07,
 the_geom)
SELECT
 a.gid,
 (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom AS the_geom
FROM bfn.mv_dlm07_orig a, bfn.mv_dlm07_orig b
WHERE ST_OVERLAPS(a.the_geom,b.the_geom)=true AND 
INTERSECTS(a.the_geom,b.the_geom)=true;


DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='LINESTRING';
DELETE FROM bfn.mv_dlm07_olvg
WHERE GeometryType(the_geom)='POINT';

CREATE TABLE bfn.mv_dlm07_ol
(
 gid serial NOT NULL,
 gid_mv_dlm07 integer,
 x_centroid numeric,
 y_centroid numeric,
 area numeric,
 perimeter numeric
);
SELECT 
AddGeometryColumn('bfn','mv_dlm07_ol','the_geom','31467','MULTIPOLYGON',2);


ALTER TABLE bfn.mv_dlm07_ol DROP CONSTRAINT enforce_geotype_the_geom;

INSERT INTO bfn.mv_dlm07_ol (
 gid_mv_dlm07,
 x_centroid,
 y_centroid,
 area,
 perimeter,
 the_geom)
SELECT
 a.gid_mv_dlm07,
 st_x(st_centroid(a.the_geom)) as x_centroid,
 st_y(st_centroid(a.the_geom)) as y_centroid,
 st_area(a.the_geom) as area,
 st_perimeter(a.the_geom) as perimeter,
 a.the_geom as the_geom
FROM bfn.mv_dlm07_olvg a
GROUP BY st_x(st_centroid(a.the_geom)), st_y(st_centroid(a.the_geom)), 
st_area(a.the_geom), st_perimeter(a.the_geom), a.the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, 
a.gid_mv_dlm07 asc;


Regards,

Birgit.



Obe, Regina schrieb:

Birgit,

So you are saying ST_Centroid sometimes returns geometry collections.
Hmm I don't see how that is possible unless there is bug somewhere.

Can you provide an example of that.  
Is attached - these are the ones which return GeometryCollection after 
ST_Centroid. I had to remove the attributes because of privacy reasons.



Regarding the below query - it
would be a bit easier to follow if you did a subselect first.

Something like

 SELECT
  abdump.gid,
  abdump.objart_07,
 abdump.cat_07,
ST_X(ST_Centroid(abdump.newgeom)) As x_centroid,
ST_Y(ST_Centroid(abdump.newgeom)) As y_centroid
ST_Area(ST_Centroid(abdump.newgeom)) As area,
ST_Perimeter(ST_Centroid(abdump.newgeom)) as perimeter
FROM (SELECT (ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom As newgeom, a.gid, a.objart_07, a.cat_07, a.the_geom

as a_the_geom, b.the_geom as b_the_geom
FROM 
 bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true

) As abdump
GROUP BY ST_X(ST_Centroid(abdump.newgeom)), 
ST_Y(ST_Centroid(abdump.newgeom)), ST_Area(ST_Centroid(abdump.newgeom)),


ST_Perimeter(ST_Centroid(abdump.newgeom)), abdump.gid, abdump.objart_07,
abdump.cat_07, a_the_geom, b_the_geom
ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid
asc;

Hope that helps,
Regina

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Tuesday, October 14, 2008 6:56 AM
To: PostGIS Users Discussion
Subject: [postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

Dear list,

I have a SELECT statement in which I try to group by the polygons 
resulting from a self intersection. After I realized that I can't group 
by the_geom because this would group polygons which are only similar but


not equal, I decided to group by the area, the perimeter and the x- and 
y-coordinates of the centroid of every polygon. The SELECT statement I 
wrote, worked so far for some test datasets and showed seemingly 
reasonable results. But fo

[postgis-users] GeometryCollection after ST_Centroid(ST_Dump)

[Birgit Laggner]

Dear list,

I have a SELECT statement in which I try to group by the polygons 
resulting from a self intersection. After I realized that I can't group 
by the_geom because this would group polygons which are only similar but 
not equal, I decided to group by the area, the perimeter and the x- and 
y-coordinates of the centroid of every polygon. The SELECT statement I 
wrote, worked so far for some test datasets and showed seemingly 
reasonable results. But for at least one of my datasets, the statement 
does not work, because the ST_Centroid produces some GeometryCollections 
instead of POINTS. What could cause that problem and how can I solve it???


This is my statement:

SELECT
 a.gid,
 a.objart_07,
 a.cat_07,
 ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) as x_centroid,
 ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)) as y_centroid,

 ST_Area((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) as area,
 ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom) 
as perimeter,

 (ST_Dump(ST_Intersection(a.the_geom, b.the_geom))).geom as the_geom
FROM bfn.test_mv_dlm07 a, bfn.test_mv_dlm07 b
WHERE ST_Overlaps(a.the_geom,b.the_geom)=true AND 
Intersects(a.the_geom,b.the_geom)=true
GROUP BY ST_X(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), 
ST_Y(ST_Centroid((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom)), ST_Area((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), ST_Perimeter((ST_Dump(ST_Intersection(a.the_geom, 
b.the_geom))).geom), a.gid, a.objart_07, a.cat_07, a.the_geom, b.the_geom

ORDER BY x_centroid asc, y_centroid asc, area asc, perimeter asc, a.gid asc;

The ST_Dump results in POLYGONs, LINESTRINGs and POINTs.

Thank you for any help!

Regards,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] SELECT problem

[Birgit Laggner]

Hi again,

no, I am not... Today, I tried the query again, and I deleted all 
unnessecary attribute fields, so the query looked really like the one I 
sent to the list. And now, it seems like the duplicates originate from 
the first part of the query, where they are absolutely ok because the 
source table contains duplicates. I could swear, yesterday, it was the 
other way round... :-)


Sorry for my stupidity :-) Regards,

Birgit.


Birgit Laggner schrieb:

Hi Regina,

yes, I am :-)

Regards,

Birgit.

Obe, Regina schrieb:

 Birgit,

Looks fine to me actually.

So are you saying this query generates duplicates?

select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a
except
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07
limit 100;

Hope that helps,
Regina

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Tuesday, September 23, 2008 7:42 AM
To: PostGis_Mailinglist
Subject: [postgis-users] SELECT problem

Dear list,

probably, I'm just to unexperienced to see what I'm doing wrong, but 
that doesn't change that I need help :-)


I want to select a test dataset out of a bigger dataset (a). First 
part of the test dataset should be the first 100 geometries of (a) 
which have


ids also occuring in a second dataset (b). Second part should be 100 
geometries of (a) whose ids don't match with ids of (b). This is my 
sql statement so far:


insert into bfn.nw_inv07_oc_rep_test
select
  a.gid,
  a.feldblocki,
  a.the_geom
 from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07;
insert into bfn.nw_inv07_oc_rep_test
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a
except
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07
limit 100;

The first part of the insert works fine, but the result of the second 
insert looks strange for me: some rows double or even triple, while 
in the origin datasets, there are no duplicate rows.


Anybody who could explain what's happening during my query?

Thanks a lot,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

  




--
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: [EMAIL PROTECTED]
Internet: www.vti.bund.de

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] SELECT problem

[Birgit Laggner]

Hi Regina,

yes, I am :-)

Regards,

Birgit.

Obe, Regina schrieb:

 Birgit,

Looks fine to me actually.

So are you saying this query generates duplicates?

select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a
except
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07
limit 100;

Hope that helps,
Regina

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Tuesday, September 23, 2008 7:42 AM
To: PostGis_Mailinglist
Subject: [postgis-users] SELECT problem

Dear list,

probably, I'm just to unexperienced to see what I'm doing wrong, but 
that doesn't change that I need help :-)


I want to select a test dataset out of a bigger dataset (a). First part 
of the test dataset should be the first 100 geometries of (a) which have


ids also occuring in a second dataset (b). Second part should be 100 
geometries of (a) whose ids don't match with ids of (b). This is my sql 
statement so far:


insert into bfn.nw_inv07_oc_rep_test
select
  a.gid,
  a.feldblocki,
  a.the_geom
 from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07;
insert into bfn.nw_inv07_oc_rep_test
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a
except
select
  a.gid,
  a.feldblocki,
  a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07
limit 100;

The first part of the insert works fine, but the result of the second 
insert looks strange for me: some rows double or even triple, while in 
the origin datasets, there are no duplicate rows.


Anybody who could explain what's happening during my query?

Thanks a lot,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

  


--
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: [EMAIL PROTECTED]
Internet: www.vti.bund.de

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] SELECT problem

[Birgit Laggner]

Dear list,

probably, I'm just to unexperienced to see what I'm doing wrong, but 
that doesn't change that I need help :-)


I want to select a test dataset out of a bigger dataset (a). First part 
of the test dataset should be the first 100 geometries of (a) which have 
ids also occuring in a second dataset (b). Second part should be 100 
geometries of (a) whose ids don't match with ids of (b). This is my sql 
statement so far:


insert into bfn.nw_inv07_oc_rep_test
select
 a.gid,
 a.feldblocki,
 a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07;
insert into bfn.nw_inv07_oc_rep_test
select
 a.gid,
 a.feldblocki,
 a.the_geom
from bfn.nw_inv07_oc_rep a
except
select
 a.gid,
 a.feldblocki,
 a.the_geom
from bfn.nw_inv07_oc_rep a, bfn.nw_inv07_ol_test b
where a.gid=b.gid_inv07
limit 100;

The first part of the insert works fine, but the result of the second 
insert looks strange for me: some rows double or even triple, while in 
the origin datasets, there are no duplicate rows.


Anybody who could explain what's happening during my query?

Thanks a lot,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Memory problem with ST_Within

[Birgit Laggner]

Thanks to everybody who tried to help me solving this problem!! 
Meanwhile, we (actually, it was our admin :-) ) made it by programming a 
loop in plpsql as a postgresql function. With the loop, the query ran 
overnight without problems and my select result is exactly as I wanted 
it to be.


Regards,

Birgit.



Burgholzer,Robert schrieb:

Birgit,
My guess is that the part of the query that is causing the memory dump
is not the GIST index, but rather the within query, which must iterate
through all of your points.

That said, if you wish to determine if the GIST index is causing
problems, you could do the && in a separate query, store the results,
along with ID columns to map your results in a temp table, and then do
the standard "within" query (not "st_within" which forces the index
usage) on this subset of records whose bounding boxes overlap.

I suspect that you will still find the memory dump problems.  


One other approach that you could use, to winnow down the field of
candidates, is to add a second geometry field to your polygon table, and
place a simplified version of the geometries in there (using
simplify(geomcol, tolerance).  Some times, the geometries can be
simplified without losing any relevant spatial information.  My
experience is that the greatest way to reduce query intensity, and
improve speed as well as eliminate memory trouble is to reduce the
number of points that you are analyzing.

r.b.

Robert W. Burgholzer
Surface Water Modeler
Office of Water Supply and Planning
Virginia Department of Environmental Quality
[EMAIL PROTECTED]
804-698-4405
Open Source Modeling Tools:
http://sourceforge.net/projects/npsource/

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Birgit Laggner
Sent: Monday, August 25, 2008 6:34 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Memory problem with ST_Within

Frank Koormann schrieb:
  

* Birgit Laggner <[EMAIL PROTECTED]> [080821 09:59]:
  


Hallo Regina, hallo Frank,

thanks for your suggestions!

Leaving out the_geom didn't solve the problem - I still ran out of
  
memory. 
  
I attached my postgresql.conf settings and the result of the EXPLAIN 
ANALYSE SELECT to this e-mail.

  

Hm, the explain analyse finishes successfully. Explain analyse


actually
  

execute the query (in your case in 675 milliseconds), so the problem


seems
  

to be returning the results to pgAdmin, assuming that the queries run


on
  

the same data set.

Your memory related settings are already significantly increased.
Without knowledge about your system if too high.

Other points: 
- What is the full error message?

- Do you have more success when using psql instead of pgAdmin?

Regards,

Frank
  


Hallo Frank,

this is actually a misunderstanding: of course, the Explain analyse did 
not run on the full dataset either, therefore I started the Explain 
analyse for my test dataset, which I used to test if my query is working


at all.

The full error message is:
ERROR: Memory exhausted
SQL Status:53200
Detail: Failed on request of size 32.

I'm still testing if psql is more successful then pgAdmin. Results 
tomorrow...


Thank you!

Regards,

Birgit.

Mark Cave-Ayland schrieb:
  

Hi Birgit,

If PostgreSQL stops due to lack of memory, it generally dumps 
information about memory usage into the server log file before it 
terminates. Can you see any such information in your PostgreSQL log 
file? If so, posting it here would help greatly, along with the output



  

of "SELECT postgis_full_version()".


ATB,

Mark. 


Hallo Mark,

this is my postgis version:

"POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" 
USE_STATS"


The memory usage information of the PostgreSQL log file, I attached to 
the e-mail (pg.log).


Thank you!

Regards,

Birgit.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

  


--
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: [EMAIL PROTECTED]
Internet: www.vti.bund.de

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Memory problem with ST_Within

[Birgit Laggner]

Frank Koormann schrieb:

* Birgit Laggner <[EMAIL PROTECTED]> [080821 09:59]:
  

Hallo Regina, hallo Frank,

thanks for your suggestions!

Leaving out the_geom didn't solve the problem - I still ran out of memory. 
I attached my postgresql.conf settings and the result of the EXPLAIN 
ANALYSE SELECT to this e-mail.



Hm, the explain analyse finishes successfully. Explain analyse actually
execute the query (in your case in 675 milliseconds), so the problem seems
to be returning the results to pgAdmin, assuming that the queries run on
the same data set.

Your memory related settings are already significantly increased.
Without knowledge about your system if too high.

Other points: 
- What is the full error message?

- Do you have more success when using psql instead of pgAdmin?

Regards,

Frank
  

Hallo Frank,

this is actually a misunderstanding: of course, the Explain analyse did 
not run on the full dataset either, therefore I started the Explain 
analyse for my test dataset, which I used to test if my query is working 
at all.


The full error message is:
ERROR: Memory exhausted
SQL Status:53200
Detail: Failed on request of size 32.

I'm still testing if psql is more successful then pgAdmin. Results 
tomorrow...


Thank you!

Regards,

Birgit.

Mark Cave-Ayland schrieb:

Hi Birgit,

If PostgreSQL stops due to lack of memory, it generally dumps 
information about memory usage into the server log file before it 
terminates. Can you see any such information in your PostgreSQL log 
file? If so, posting it here would help greatly, along with the output 
of "SELECT postgis_full_version()".



ATB,

Mark. 

Hallo Mark,

this is my postgis version:

"POSTGIS="1.3.3" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007" 
USE_STATS"


The memory usage information of the PostgreSQL log file, I attached to 
the e-mail (pg.log).


Thank you!

Regards,

Birgit.


pg.log
Description: application/sas-log
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Memory problem with ST_Within

[Birgit Laggner]

Hallo,

I have 2 large datasets (the first one consists of polygons and has 
15099748 rows and the second is a point geometry with 76421902 rows) and 
would like to sort out which points are located within which polygons. 
The aim is to calculate some statistics (count, min, max, avg) for the 
points in every polygon. When I tested the query with a small subset, 
everything worked fine and the result looked the way I expected. But 
applied on the whole datasets, the query runs for approximately 10 mins 
and then stops because the server is out of memory.


Some days earlier, I already tried to run the query and had not yet 
created gists for both datasets. At this occasion, the query ran for 
more then 4 days without any result until I stopped the query manually.


Here is my query:

SELECT
polygon_id,
count(hoehe) as hoehe_count,
min(hoehe) as hoehe_min,
max(hoehe) as hoehe_max,
avg(hoehe) as hoehe_avg,
count(neigung) as neig_count,
min(neigung) as neig_min,
max(neigung) as neig_max,
avg(neigung) as neig_avg,
count(exposition) as expos_count,
min(exposition) as expos_min,
max(exposition) as expos_max,
avg(exposition) as expos_avg,
p.the_geom
FROM bfn.ni_hoehendaten h, bfn.ni_polygone2 p
WHERE ST_Within(h.the_geom, p.the_geom)
GROUP BY p.polygon_id, p.the_geom
ORDER BY p.polygon_id ASC;

Does anybody know a method to reduce the memory usage? Or are there 
other suggestions how this problem could be solved?


Thanks a lot,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

Re: [postgis-users] Problem sorting polygons with generate_series

[Birgit Laggner]

Hi Regina,

I did not yet understand how your code exactly works, but it does...

Thank you very much for your prompt and helpful reply!

Birgit.


Paragon Corporation schrieb:

Hi Birgit,

This is a tricky thing to do in SQL.  The easiest way I can think of of
doing this (may not be the most efficient) is to use a correlated subquery.
For this you don't really need the temp count table you set up.  Although
you do need some field that can order the records in some arbitrary way

Something like this

--for your original table - just add a dummy serial if you don't have one
already
ALTER TABLE schema.table1 ADD COLUMN gid serial;
--Add a compound index on your new gid and the ID field to make this go
faster something like
CREATE UNIQUE INDEX idx_table1_id_gid
   ON schema.table1 USING btree (ID_table1, gid);

--Now insert into your new table
INSERT INTO schema.newtable (ID, sort_nr, the_geom) 
SELECT o.ID_table1 as ID_new, (SELECT COUNT(t1.gid) 
	FROM schema.table1 t1 
WHERE t1.ID_table1 = o.ID_table1 AND t1.gid

<= o.gid) As sort_nr,
 o.the_geom
FROM schema.table1 o


Hope that helps,
Regina 

 


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Birgit
Laggner
Sent: Wednesday, July 23, 2008 5:02 AM
To: PostGIS Users Discussion
Subject: [postgis-users] Problem sorting polygons with generate_series

Hi,

I have a dataset of multipolygons and some of them have identical IDs. 
Now, I would like to sort these out and give every sequence with identical

IDs a serial numbering (1,2,...). My idea was:

create table schema.newtable
(ID integer,
sort_nr smallint
);
select addgeometrycolumn
('schema','newtable','the_geom','31467','MULTIPOLYGON',2);

create temp table count1
(
ID integer,
count smallint
);

insert into count1
select ID_table1 as ID, count(ID_table1) as count from schema.table1 group
by ID having count(ID_table1) > 1 order by ID ;

insert into schema.newtable (ID, sort_nr, the_geom) select o.ID_table1 as
ID_new, generate_series(1,2) as sort_nr, o.the_geom, c.ID from count c,
schema.table1 o where o.ID_table1=c.ID order by ID_new ;

but this doesn't seem to work, because from the last SELECT, I get the
double polygons not in twofold but as four rows with identical IDs.

Anybody knows why this happens and what I should do to come to the right
solution???

Thanks,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users


___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

  


--
Dipl.-Geoökol. Birgit Laggner

Johann Heinrich von Thünen-Institut,
Bundesinstitut für Ländliche Räume, Wald und Fischerei
Institut für Ländliche Räume
Bundesallee 50
38116 Braunschweig

Johann Heinrich von Thünen-Institute
Federal Research Institute for Rural Areas, Forestry and Fisheries
Institute of Rural Areas
Bundesallee 50
D-38116 Braunschweig
Germany

Tel.: (0531) 596 - 5240
Fax: (0531) 596 - 5599
E-Mail: [EMAIL PROTECTED]
Internet: www.vti.bund.de

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

[postgis-users] Problem sorting polygons with generate_series

[Birgit Laggner]

Hi,

I have a dataset of multipolygons and some of them have identical IDs. 
Now, I would like to sort these out and give every sequence with 
identical IDs a serial numbering (1,2,...). My idea was:


create table schema.newtable
(ID integer,
sort_nr smallint
);
select addgeometrycolumn 
('schema','newtable','the_geom','31467','MULTIPOLYGON',2);


create temp table count1
(
ID integer,
count smallint
);

insert into count1
select ID_table1 as ID, count(ID_table1) as count
from schema.table1
group by ID
having count(ID_table1) > 1
order by ID
;

insert into schema.newtable (ID, sort_nr, the_geom)
select o.ID_table1 as ID_new, generate_series(1,2) as sort_nr, 
o.the_geom, c.ID

from count c, schema.table1 o
where o.ID_table1=c.ID
order by ID_new
;

but this doesn't seem to work, because from the last SELECT, I get the 
double polygons not in twofold but as four rows with identical IDs.


Anybody knows why this happens and what I should do to come to the right 
solution???


Thanks,

Birgit.

___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users