Re: [postgis-users] Problem with 3D polygon intersection test
Maybe ST_X, ST_Y and ST_Z ?
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Ed Linde
Envoyé : mardi 30 octobre 2012 11:14
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Problem with 3D polygon intersection test
Hi Nicolas,
Not quite sure what to make of the output from ST_DumpPoints, how do I
transform it to just raw x,y,z coordinates?
select st_astext(geomtext) from small_tris where id=0;
st_astext
---
POLYGON Z ((10 10 805,20 10 809,10 20 814,10 10 805))
(1 row)
bounds=# select id, st_dumpPoints( geomtext ) from small_tris where id=0;
id |st_dumppoints
+--
0 | ("{1,1}",0101802440244000288940)
0 | ("{1,2}",0101803440244000488940)
0 | ("{1,3}",0101802440344000708940)
0 | ("{1,4}",0101802440244000288940)
(4 rows)
Thanks,Ed
On Tue, Oct 30, 2012 at 11:05 AM, Ed Linde wrote:
Ok thanks!
On Tue, Oct 30, 2012 at 11:04 AM, Nicolas Ribot
wrote:
st_dumpRings will return the rings (closed linestrings)
composing the polygon (exterior first, then interiors)
and st_dumpPoints will return all the points forming the rings,
in the right order.
Nicolas
On 30 October 2012 10:56, Ed Linde wrote:
Ok, for a polygon, is there a good postgis function
that can return the edges to me in the
right sequence? Or at least the points that constitute
the polygon in the right order, so I can
generate the edges in a script?
Thanks,
Ed
On Tue, Oct 30, 2012 at 10:46 AM, Ed Linde
wrote:
Ah drat! Ok then I have to basically do edge
intersection tests by myself.
Ah well. bad news! :(
On Tue, Oct 30, 2012 at 10:44 AM, Nicolas Ribot
wrote:
Hi,
st_intersection is only suitable for 2D
operations. Third dimension is not taken into account.
Nicolas
On 30 October 2012 09:44, Ed Linde
wrote:
Hi All,
Is there a simpler test I can do? I am
quite sure that I am probably not using the right
function in my SQL, or not doing the
intersection test correctly. Is there any simpler
way to test this 3D intersection? Am
waiting on the postgis gurus to come along and
give me some ideas, coz I am at my wits
ends on this one! :) The polygons look good
to me when I visualize them, but don't
get how all triangles are found as intersecting?
Anyway will wait.
Cheers,
Ed
On Tue, Oct 30, 2012 at 9:00 AM, Ed
Linde wrote:
Hi All,
I am having some troubles getting the
ST_Intersect function to work with 3D polygons.
I have a table to hold triangles as
polygons in "small_tris"
Another table for holding the polygonal
faces of a 3D voronoi diagram in "vfaces"
So now I am trying to do an
intersection between the triangles and the voronoi faces
to get the triangles that intersect
with the faces. And it seems that *ALL* triangles
are intersecting. I have attached a PDF
where I have plotted the triangles and the
voronoi cells. so the box around in red
and blue with a major face going through is
the voronoi diagram. The Voronoi
partition in 3D is shown at the bottom
Re: [postgis-users] Difficult Problem with Polygons
Hi, 1. Does st_touches not work for that ? 2. What do you mean by below ? south from the line ? St_distance should help you to find the closer one from Q. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date: lun. 29/10/2012 09:33 À: PostGIS Users Discussion Objet : [postgis-users] Difficult Problem with Polygons Hi All, I need help with 2 hard problems. I store triangles in a table as POLYGON. 1. I want to know for a given triangle, which triangles share an edge (adjacent) with this triangle. 2. Then I have a line that cuts through a triangle's face and a point Q, away from this line and triangle. I would like to know amongst the neighbours of the triangle (calculated in 1), which ones fall *below* the line and closer to the point Q (where Q is the generator of a Voronoi cell). And also the set of triangles that are *above* the line and further from Q. Any suggestions as to how I can go about achieving this in postgis 2.0? Cheers, Ed <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] identify rows in a shapefile that have illegal characters for UTF-8 encoding
Hello, I often play with postgis data in arcgis, using original shapefiles encoded using LATIN1 and I have no characters problem. My major problem comes from null values which prevent arcgis to display data attributes but, usually, all my geometries are here and I can even play with symbology without the possibility to display the data I'm working on. Did you check null values or did you try to import them in arcgis as a request layer using the where clause to exclude null data ? Hugues. -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Mark Volz Envoyé : lundi 22 octobre 2012 22:01 À : postgis-users@postgis.refractions.net Objet : [postgis-users] identify rows in a shapefile that have illegal characters for UTF-8 encoding Hello, I am trying to load my parcels into PostGIS, which will eventually be consumed by MapServer, and ArcGIS. Initially when loaded my data I received a warning that I should change my encoding from UTF-8 to LATIN1. Doing so allowed me to load data into PostGIS however, I could not consume the data in ArcGIS. So from what I observed, I need to stick with UTF-8 encoding. I have determined that the field for the legal description in my parcels is stopping me from loading the shapefile into PostGIS using UTF-8. How can I find out which rows in my shapefile have illegal characters for UTF-8 encoding? Thank You Mark Volz GIS Specialist ___ 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] How to make some geometry column visible to Qgis
Your view is defined by a sql select so you should try to leave this select as
is except for the where clause where you could add something like : and
c.relname not like 'your_table_name'
I tried it with one of my table and this was ok, the table disappeared from the
geometry_columns view
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pietro
Rossin
Envoyé : lundi 22 octobre 2012 11:40
À : postgis-users@postgis.refractions.net
Objet : Re: [postgis-users] How to make some geometry column visible to Qgis
Hello Hugues
in my database I have both views and a main table on which these views pick
data from.
All of them have their own geometry column registered into the geometry column
set by postgis
My problem is that the main table (a huge one with mixed geometry data type)
has its own geometry column.
When qgis starts the connection to this db automatically looks into the
geometry column set and find the geom.column relative to the main table, so it
searches that table for the geometry type and take a lot of time to do it...
How can I hide this geometry column?
Thnx
Pietro
2012/10/22 Francois Hugues [via PostGIS] <[hidden email]>
Hello
"This because I cannot remove the entities geometry column unless I
remove the entity table first.
And if I don't remove (or make invisible to qgis if possible) the
entities geometry column it slow down the qgis connection."
Did you try to edit the view, using the where clause to exclude the
tables you don't want to be seen by postgis ?
Hugues.
De : [hidden email]
<http://user/SendEmail.jtp?type=node&node=5000686&i=0> [mailto:[hidden email]
<http://user/SendEmail.jtp?type=node&node=5000686&i=1> ] De la part de Pietro
Rossin
Envoyé : lundi 22 octobre 2012 09:25
À : [hidden email]
<http://user/SendEmail.jtp?type=node&node=5000686&i=2>
Objet : Re: [postgis-users] How to make some geometry column visible to
Qgis
DELETE FROM geometry_columns WHERE f_table_name = 'puntigronda';
Query returned successfully: 0 rows affected, 0 ms execution time.
INSERT INTO geometry_columns (f_table_catalog,f_table_schema,
f_table_name, f_geometry_column, coord_dimension, srid,type)Â
 VALUES ('','', 'puntigronda', 'the_geom', 3, 3004,'POINTS');
Query returned successfully: 0 rows affected, 16 ms execution time.
select relkind from pg_class where relname = 'geometry_columns';
returns:
"v"
What is this?
By the way
If my technique works, is it still better to explode the "entities"
table in one of this cases:
a) single layers
b) same kind of geometries
in both cases table permanent.
This because I cannot remove the entities geometry column unless I
remove the entity table first.
And if I don't remove (or make invisible to qgis if possible) the
entities geometry column it slow down the qgis connection.
Thanks for your help
Pietro
2012/10/22 Sandro Santilli [via PostGIS] <[hidden email]
<http://user/SendEmail.jtp?type=node&node=5000685&i=0> >
On Mon, Oct 22, 2012 at 08:40:07AM +0200, Pietro Rossin wrote:
> DELETE FROM geometry_columns WHERE f_table_name =
'puntigronda';
...
> Is there something wrong?
What's wrong is that the "geometry_columns" table should be a
view,
in PostGIS-2.0. If it is a view, your DELETE and INSERT and
UPDATE
to it should have no effect.
Can you verify this first ?
The DELETE/INSERT/UPDATE commands all return the number of
affected
rows. If PostGIS is correctly installed you should alwyas get
0,
no matter what you try to insert/delete/update.
Also check:
select relkind from pg_class where relname =
'geometry_columns';
--strk;
 http://www.cartodb.com - Map, analyze and build applications
with your data
                    ~~
http://strk.keybit.netÂ
__
Re: [postgis-users] How to make some geometry column visible to Qgis
Hello
"This because I cannot remove the entities geometry column unless I remove the
entity table first.
And if I don't remove (or make invisible to qgis if possible) the entities
geometry column it slow down the qgis connection."
Did you try to edit the view, using the where clause to exclude the tables you
don't want to be seen by postgis ?
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pietro
Rossin
Envoyé : lundi 22 octobre 2012 09:25
À : postgis-users@postgis.refractions.net
Objet : Re: [postgis-users] How to make some geometry column visible to Qgis
DELETE FROM geometry_columns WHERE f_table_name = 'puntigronda';
Query returned successfully: 0 rows affected, 0 ms execution time.
INSERT INTO geometry_columns (f_table_catalog,f_table_schema, f_table_name,
f_geometry_column, coord_dimension, srid,type)Â
 VALUES ('','', 'puntigronda', 'the_geom', 3, 3004,'POINTS');
Query returned successfully: 0 rows affected, 16 ms execution time.
select relkind from pg_class where relname = 'geometry_columns';
returns:
"v"
What is this?
By the way
If my technique works, is it still better to explode the "entities" table in
one of this cases:
a) single layers
b) same kind of geometries
in both cases table permanent.
This because I cannot remove the entities geometry column unless I remove the
entity table first.
And if I don't remove (or make invisible to qgis if possible) the entities
geometry column it slow down the qgis connection.
Thanks for your help
Pietro
2012/10/22 Sandro Santilli [via PostGIS] <[hidden email]>
On Mon, Oct 22, 2012 at 08:40:07AM +0200, Pietro Rossin wrote:
> DELETE FROM geometry_columns WHERE f_table_name = 'puntigronda';
...
> Is there something wrong?
What's wrong is that the "geometry_columns" table should be a view,
in PostGIS-2.0. If it is a view, your DELETE and INSERT and UPDATE
to it should have no effect.
Can you verify this first ?
The DELETE/INSERT/UPDATE commands all return the number of affected
rows. If PostGIS is correctly installed you should alwyas get 0,
no matter what you try to insert/delete/update.
Also check:
select relkind from pg_class where relname = 'geometry_columns';
--strk;
 http://www.cartodb.com - Map, analyze and build applications with
your data
                    ~~
http://strk.keybit.netÂ
___
postgis-users mailing list
[hidden email]
http://postgis.refractions.net/mailman/listinfo/postgis-users
If you reply to this email, your message will be added to the
discussion below:
http://postgis.17.n6.nabble.com/How-to-make-some-geometry-column-visible-to-Qgis-tp5000570p5000684.html
To unsubscribe from How to make some geometry column visible to Qgis,
click here.
NAML
View this message in context: Re: How to make some geometry column visible to
Qgis
Sent from the PostGIS - User mailing list archive
at Nabble.com.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Question: calculating the slope with PostGIS 2.0
This is a select query. If you want to create a new table try Create table mytable as Myselect Hugues Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre LACROIX Date: ven. 19/10/2012 15:42 À: PostGIS Users Discussion Objet : Re: [postgis-users] Question: calculating the slope with PostGIS 2.0 Thanks. Does that create a new slope raster in the database? After refreshing there is nothing new. 2012/10/18 Bborie Park > Pierre, > > The pixel type isn't 'Integer'. The list of valid pixel types are: > > 1BB - 1-bit boolean > > 2BUI - 2-bit unsigned integer > > 4BUI - 4-bit unsigned integer > > 8BSI - 8-bit signed integer > > 8BUI - 8-bit unsigned integer > > 16BSI - 16-bit signed integer > > 16BUI - 16-bit unsigned integer > > 32BSI - 32-bit signed integer > > 32BUI - 32-bit unsigned integer > > 32BF - 32-bit float > > 64BF - 64-bit float > > So, you'd call ST_Slope() with something like. > > SELECT ST_Slope(rast, 1, '32BUI') from Raster1; > > > -bborie > > On 10/18/2012 12:14 PM, Pierre LACROIX wrote: > > Hi, > > > > I would like to generate the slope from a DEM. > > The DEM is called Raster1. It has one band and holds integer values. > >>From the the SQL Editor (in the PG Admin) the following command displays > > values but does not generate an output slope raster: > > > > SELECT ST_Slope(rast, 1,'Integer') from Raster1; > > > > What is the syntax to generate the slope raster? > > > > Thanks in advance. > > > > Pierre > > > > > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > -- > Bborie Park > Programmer > Center for Vectorborne Diseases > UC Davis > 530-752-8380 > bkp...@ucdavis.edu > ___ > 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] "service" field in postgres
I left it blank and it works To import shape to postgis you will have to use shp2pgsql Did you made an install of postgis : it works with postgresql but is not included in postgresql. It is another install process. Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de chathura silva Envoyé : jeudi 11 octobre 2012 19:57 À : PostGIS Users Discussion Objet : Re: [postgis-users] "service" field in postgres I have a shapefile of road network. Do you know how to import it to postgreSQL ? ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Select polygons within polygon with PostGIS 2.0
Hello, I don't know how your differents layers has been made but your problem certainly rely on difference with boundaries even if there are very little different and just one point not into the county, your district will not be select. If you want to correct that you could use a buffer (st_within(o.geom,st_buffer(k.geom,XX)) big enough to correct the little differences of the boundaries but not too big although you may select too many districts. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Michal Zimmermann Date: mer. 10/10/2012 10:27 À: postgis-users@postgis.refractions.net Objet : [postgis-users] Select polygons within polygon with PostGIS 2.0 Hi to all, I have a relation representing all the counties in my country and the other one containing all the districts - e. g. each and every county is composed of several districts. How do I select them with spatial query? I tried with select nazorp from kraje as k, orp_wgs as o WHERE ST_Within(o.geom,k.geom) AND k.nazev = 'Liberecký' // name of the county but had wrong results returned (well, they are probably not wrong, they are just not what I expected them to be). ST_Within only returns districts that don't share a boundary with the county, but I need to get all the districts within the county. Is that possible? I haven't found any built-in function suitable for my needs yet. -- Michal Zimmermann (zimmi) WWW: http://www.zimmi.cz ___ 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] Selecting which table column to update
Hello, There are a lot of syntax error in your query : the "=" with nothing, the use of case for many columns, the lack of from before your subselect. Hard to say what is the error and what exactly you want to do (at least for me) without knowledge of how your data is made. I don't understand what value you want to write in the updated column but I am not sure you need to use case but something like this could be ok : update pipe set branch_right_id = bob.edge_data.edge_id from ( select bob.edge_data.edge_id from bob.edge_data, num_search, pipe where st_intersects(bob.edge_data.geom, num_search.the_geom9)) --intersection of max edge collected in pipe table and pipe.id = 10 ) as foo where st_centroid(bob.edge_data.geom) >> num_search.the_geom9 and bob.edge_data.edge_id <> pipe.edge and pipe.id =10 Remember that what you do when you run an update query can not be undone unless you have a save of your original data. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Bob Pawley Date: lun. 24/09/2012 19:42 À: postgis-users@postgis.refractions.net Objet : [postgis-users] Selecting which table column to update Hi I am attempting to update a table column that is to be determined by a linestring being above, below or to the right of an intersection. update pipe set case when st_centroid(bob.edge_data.geom) >> num_search.the_geom9 where bob.edge_data.edge_id <> pipe.edge and pipe.id =10 then branch_right_id = when st_centroid(bob.edge_data.geom) |>> num_search.the_geom9 bob.edge_data.edge_id <> pipe.edge and pipe.id =10 then branch_up_id = when st_centroid(bob.edge_data.geom) <<| num_search.the_geom9 bob.edge_data.edge_id <> pipe.edge and pipe.id =10 then branch_down_id = end (select bob.edge_data.edge_id from bob.edge_data, num_search, pipe where st_intersects(bob.edge_data.geom, num_search.the_geom9)) --intersection of max edge collected in pipe table where pipe.id = 10; Following is the error- ERROR: syntax error at or near "case" LINE 3: case when st_centroid(bob.edge_data.geom) >> num_search.the... Can this be done? Bob <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Navteq import
Hello, Do you have a unique id ? The problem could be here. You should also try with openjump. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de "Benjamin Rückert" Date: ven. 21/09/2012 12:29 À: postgis-users@postgis.refractions.net Objet : [postgis-users] Navteq import Hello, Has someone experience with Navteq RDF data? I've loaded the europe data in postgresql/postGIS via RDF installer (part of rdf viewer). I ve tried to visualize the Data in ArcGIS and QGIS but it was not possible to connect the database because of the index data type (bigint). Has someone an idea to get rid of the problem? Are there any Opensource solutions for visualization RDF data (imported in Postgresql)? Which steps I have to pass through after the import for getting a spatial enable database? Thank you for your reading and hopefully answering? Ben ___ 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] raster loading and ST_Value performance
Hello, I'm not really what you can call an expert, but I can try to help you Q1 : Tiling will help you if you make an index on your raster table (using GIST and st_convexhull). I don't see any index in what you described. Q2 : Tiling +Index will speed up your query. You should also reduce time using st_intersects(rast,the_geom) in the where clause. Hugues PS : My mail problem should be resolved so if anyone see this mail as a new thread, please tell me. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Phil Hurvitz Date: mer. 19/09/2012 03:11 À: postgis-users@postgis.refractions.net Objet : [postgis-users] raster loading and ST_Value performance Hello PostGIS raster experts, I have loaded a raster representing slope for an area of Washington State (USA). The source was a large Imagine (img/ige) file (about 4.3 GB, 31935 x 34649 pixels at 10 m pixel size), which I copied to the db using raster2pgsql -I -C -e -Y -F -d -s 2926 -t 1000x1000 slope_ps.img gis.slope | psql dbname I used the tiling of 1000x1000 after numerous attempts to load the raster without tiling, and using several different file types (TIFF, ArcInfo grid, ArcInfo ASCII), each of which caused a Segmentation Error/Core dump. Now that I have a working raster, I can get a single point value using this query: SELECT rid, ST_Value(rast, the_geom, false) FROM slope, (SELECT the_geom FROM twins_withn_wa LIMIT 1) AS foo; rid |st_value -+- ... | 589 | 8.1965799331665 ... | (1120 rows) Time: 4768.648 ms 4 s seems like a long time to get one value from a raster. Here is the output of EXPLAIN ANALYZE, which seems to show that the nested loop is taking most of the time: QUERY PLAN -- Nested Loop (cost=0.00..312.67 rows=1120 width=164) (actual time=7.604..4736.852 rows=1120 loops=1) -> Limit (cost=0.00..0.26 rows=1 width=128) (actual time=0.005..0.005 rows=1 loops=1) -> Seq Scan on twins_withn_wa (cost=0.00..3043.02 rows=11702 width=128) (actual time=0.004..0.004 rows=1 loops=1) -> Seq Scan on slope (cost=0.00..21.20 rows=1120 width=36) (actual time=0.004..0.890 rows=1120 loops=1) Total runtime: 4737.278 ms (5 rows) I am running on a fairly beefy RHEL 6.3 machine with 16 G RAM (8 GB allocated to PostgreSQL), PostGIS 2.0.1 on PostgreSQL 9.1.1: select postgis_full_version(); postgis_full_version --- POSTGIS="2.0.1 r9979" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.6" RASTER (1 row) select version(); version -- PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit (1 row) I have two questions: Q1: What effect does different tile size have on storage and performance? Q2: We will need to be getting raster values from a large number of point tables over a large number of raster tables. Is there any method to speed up the queries? Any pointers will be greatly appreciated! -- -P. ** Philip M. Hurvitz, PhD | Research Assistant Professor | UW-CBE Urban Form Lab | 1107 NE 45th Street, Suite 535 | Box 354802 University of Washington, Seattle, Washington 98195-4802, USA phurv...@u.washington.edu | http://gis.washington.edu/phurvitz "What is essential is invisible to the eye." -de Saint-Exupéry ** ___ 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] RE : upload a folder of raster files
As explain in the documentation of raster2pgsql you have to apply raster constraints (-C option) if you want your tables to be correctly registered in the view. There is another functions to do that : http://postgis.refractions.net/docs/RT_AddRasterConstraints.html I think here is your problem. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Giannis Giakoumidakis Date: ven. 14/09/2012 18:01 À: Pierre Racine; PostGIS Users Discussion Objet : Re: [postgis-users] upload a folder of raster files Ok I created the QueryTables and I made the select to Add contraints to each table. One last question: I see no differences in the "extent geometry" column of the "raster_columns" view between all the tables I created, it is exactly the same in all. It seems like it is the same raster in every table, with different name. It shouldn't have every table different geometry or it is right the way it is now? From: Pierre Racine To: Giannis Giakoumidakis ; PostGIS Users Discussion Sent: Tuesday, September 11, 2012 8:51 PM Subject: RE: [postgis-users] upload a folder of raster files > Anyway, I need all the information missing from these > tables, to make queries. I guess there is no way to add contraints and > geometry > to all after the split. Is there any way that the splitted tables can hold > all the > information from the main table during the split? I think no. What's your > opinion? You should just read all the blog post. I explain how to add the constraints on all the tables. Pierre <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : Postgis 3 dimension geometry table
Hello, I'm not sure to understand your question. Did you try psql2shp ? You could also take a look to openjump, qgis or arcgis where you can add a query layer and then export it to shp. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de francescobocca...@libero.it Date: ven. 14/09/2012 10:47 À: postgis-users@postgis.refractions.net Objet : [postgis-users] Postgis 3 dimension geometry table Hi all, i have a question for you. I have a table with these properties: CONSTRAINT enforce_dims_geometry CHECK (st_ndims(geometry) = 3), CONSTRAINT enforce_geotype_geometry CHECK (geometrytype(geometry) = 'MULTILINESTRING'::text OR geometry IS NULL), i would like to export that table in shapefile or kml format (or any external format) but i don't find a good tool to do that. Can you help me?Is there a way to do that? Francesco ___ 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] RE : RE : How to change the SRID of a table ingeometry_columns view
Hello Nicolas, Finally, one simple question and three different ways to accomplish the same task. Even when it is simple, we can learn something to make it more simple ! I don't know what is the problem with my replies because I didn't know there was any problem. I simply use my mailing software... But I'll take a look at it. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Nicolas Ribot Date: ven. 14/09/2012 11:06 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : How to change the SRID of a table ingeometry_columns view > Hello, > > I think you do not have to change the view but only update the original table > using st_SetSRID (http://postgis.org/docs/ST_SetSRID.html) and the view > should be automatically update. > > To change the SRID from your table you will certainly have to remove > constraints first. > > Hugues. > Hi Hugues, Sorry, did not see your answer. (by the way, do you know why your replies appears as a new thread instead of appending to the original thread ?) 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
[postgis-users] RE : How to change the SRID of a table ingeometry_columns view
Hello, I think you do not have to change the view but only update the original table using st_SetSRID (http://postgis.org/docs/ST_SetSRID.html) and the view should be automatically update. To change the SRID from your table you will certainly have to remove constraints first. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Stefan Schwarzer Date: ven. 14/09/2012 09:36 À: PostGIS Users Discussion Objet : [postgis-users] How to change the SRID of a table ingeometry_columns view Hi there, I'd need to change the SRID of a table, as it is "0" in the moment, and should be something else. I searched around, but couldn't really find clear guidance on how to do this with Postgis 2.x, as the "geometry_columns" is now a view and not anymore a table. I guess there is a simple SQL for that? A UpdateGeometrySRID or so? Thanks for any hints, Stef ___ 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] RE : RE : ST_DWithin is not using SpatialIndexwithSubquery
You're welcome :) Sorry for the too fast copy & paste. Don't worry about my name. Everybody confused with it ! The first name is Hugues, you were right. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Thomas Klemmer Date: mer. 12/09/2012 09:06 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : ST_DWithin is not using SpatialIndexwithSubquery That did it! I had to alter the query a bit and I ended up with this: SELECT ST_AsText(point_pos) AS point_pos FROM PointTbl_v5 a, NebTb1 b WHERE ST_DWithin(point_pos,net_geom ,5e-05) and b.ogc_fid =500; The query return immediately and the Explain looks a lot different... "Nested Loop (cost=0.00..919.32 rows=1 width=128) (actual time=2.050..12.916 rows=8 loops=1)" " Join Filter: ((b.NebTb1 && st_expand(a.point_pos, 5e-05::double precision)) AND _st_dwithin(a.point_pos, b.net_geom, 5e-05::double precision))" " -> Index Scan using NetTbl_pk on NebTb1 b (cost=0.00..8.36 rows=1 width=847) (actual time=0.027..0.029 rows=1 loops=1)" "Index Cond: (ogc_fid = 23546)" " -> Index Scan using idx_pdb_v5 on PointTbl_v5 a (cost=0.00..854.80 rows=208 width=128) (actual time=1.173..12.669 rows=18 loops=1)" "Index Cond: (point_pos && st_expand(b.net_geom, 5e-05::double precision))" "Total runtime: 13.002 ms" Apperantly taking the ogc_fid = 500 out of the St_DWithin did the trick! Thanks Francois / Hugues !? (I'm kind of confused which one your firstname is? ) :) cheers Thomas 2012/9/11 Francois Hugues > Ok ! Give me one more try and after that someone more experienced than me > should answer your question. > > Maybe the problem comes from the sub query into the st_dwithin > > Did you try something like that ? > > SELECT ST_AsBinary(point_pos) AS point_pos, a.* > FROM PointTbl_v5 a, NebTb1 > WHERE ST_DWithin(point_pos,net_geom ,5e-05) > and NetTbl where ogc_fid =500; > > Hugues. > > > Message d'origine > De: postgis-users-boun...@postgis.refractions.net de la part de Thomas > Klemmer > Date: mar. 11/09/2012 13:01 > À: PostGIS Users Discussion > Objet : Re: [postgis-users] ST_DWithin is not using SpatialIndex > withSubquery > > Hi hugues, > > net_geom has a spatial index, the postam is just using the primary key on > this tabel since I am not useing any spatial filter to get the LINESTING > out of the table (just "where ogs_fid = 2" which is the primary key). > > The cruzial part is the seq scen on the large point table which should not > be used since the ST_DWithin is a spatial filter thus the spatial index > should be used. > > TK > > 2012/9/11 Francois Hugues > > > ** > > I think your query does not use the index on net_geom because it is not a > > spatial index (not a gist one) on the geometry, but a btree index on the > id. > > > > You should make the index (create index net_geom_gist on net_geom using > > gist(geom) ) and try again your query > > > > Hugues. > > > > > > > > -- > > *De :* postgis-users-boun...@postgis.refractions.net [mailto: > > postgis-users-boun...@postgis.refractions.net] *De la part de* Thomas > > Klemmer > > *Envoyé :* mardi 11 septembre 2012 11:09 > > *À :* postgis-users@postgis.refractions.net > > *Objet :* [postgis-users] ST_DWithin is not using SpatialIndex with > > Subquery > > > > Hi folks > > > > first of here some system informations: > > > > Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid > > > > "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc > > (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit" > > "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 > > September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" > > LIBJSON="UNKNOWN" TOPOLOGY RASTER" > > > > Database: > > > > TableName: PointTbl_v1 > > Index: idx_pdb_v1 > > NumRows:4.09806e+08 > > TableSize: 280 GB > > IndexSize: 21 GB[Gist on(point_pos)] > > > > GeometryCo:point_pos > > Type:POINT > > Dimensions: 2 > > SRID: 4326 > > > > /--- > > > > TableName:PointTbl_v5 > > Index: idx_pdb_v5 > > NumRows: 4.16218e+07 > > TableSize: 19 GB > > IndexSize: 2344 MB [Gist on(point_pos)] > > Primarykey:false > > > >
[postgis-users] RE : ST_DWithin is not using SpatialIndex withSubquery
Ok ! Give me one more try and after that someone more experienced than me should answer your question. Maybe the problem comes from the sub query into the st_dwithin Did you try something like that ? SELECT ST_AsBinary(point_pos) AS point_pos, a.* FROM PointTbl_v5 a, NebTb1 WHERE ST_DWithin(point_pos,net_geom ,5e-05) and NetTbl where ogc_fid =500; Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Thomas Klemmer Date: mar. 11/09/2012 13:01 À: PostGIS Users Discussion Objet : Re: [postgis-users] ST_DWithin is not using SpatialIndex withSubquery Hi hugues, net_geom has a spatial index, the postam is just using the primary key on this tabel since I am not useing any spatial filter to get the LINESTING out of the table (just "where ogs_fid = 2" which is the primary key). The cruzial part is the seq scen on the large point table which should not be used since the ST_DWithin is a spatial filter thus the spatial index should be used. TK 2012/9/11 Francois Hugues > ** > I think your query does not use the index on net_geom because it is not a > spatial index (not a gist one) on the geometry, but a btree index on the id. > > You should make the index (create index net_geom_gist on net_geom using > gist(geom) ) and try again your query > > Hugues. > > > > -- > *De :* postgis-users-boun...@postgis.refractions.net [mailto: > postgis-users-boun...@postgis.refractions.net] *De la part de* Thomas > Klemmer > *Envoyé :* mardi 11 septembre 2012 11:09 > *À :* postgis-users@postgis.refractions.net > *Objet :* [postgis-users] ST_DWithin is not using SpatialIndex with > Subquery > > Hi folks > > first of here some system informations: > > Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid > > "PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc > (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit" > "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 > September 2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" > LIBJSON="UNKNOWN" TOPOLOGY RASTER" > > Database: > > TableName: PointTbl_v1 > Index: idx_pdb_v1 > NumRows:4.09806e+08 > TableSize: 280 GB > IndexSize: 21 GB[Gist on(point_pos)] > > GeometryCo:point_pos > Type:POINT > Dimensions: 2 > SRID: 4326 > > /--- > > TableName:PointTbl_v5 > Index: idx_pdb_v5 > NumRows: 4.16218e+07 > TableSize: 19 GB > IndexSize: 2344 MB [Gist on(point_pos)] > Primarykey:false > > GeometryCo: point_pos > Type: POINT > Dimensions:2 > SRID: 4326 > > /--- > > TableName:NetTbl > Index:idx_net > NumRows: 270615 > TableSize: 195 MB > IndexSize: 17 MB > Primarykey: NetTbl_pk > > > GeometryCo:net_geom > Type:LINESTRING > Dimensions: 2 > SRID: 4326 > > Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1 > that are close or on a LineString in NetTbl; > Here ist the Query I'm trying to run: > > SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type > FROM PointTbl_v5 > WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid = > 500) ,5e-05); > > This query takes 319005 msec to return which is very slow due to the a seq > scan done on PoinDB_v5 > > EXPLAIN ANALYSE > > "Seq Scan on PointTbl_v5 (cost=108.36..10013364820.01 > rows=13873927 width=202) (actual time=199926.978..318895.494 rows=5 > loops=1)" > " Filter: st_dwithin(point_pos, $0, 5e-05::double precision)" > " InitPlan 1 (returns $0)" > "-> Index Scan using NetTbl_pk on NetTbl (cost=0.00..8.36 rows=1 > width=847) (actual time=2.069..2.075 rows=1 loops=1)" > " Index Cond: (ogc_fid = 2)" > "Total runtime: 318895.583 ms" > > The query: > > SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2 > > returns in 16 ms; > > If I instert the Geometry by Hand into the first query like this: > > SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate, > district, town > FROM PointTbl_v5 > WHERE ST_DWithin(point_pos, ST_GeomFromText( > 'LINESTRING()',4326),5e-05); > > This query return in 63ms on the small table and 766ms on the bigger table. > > EXPLAIN ANALYSE > > "Index Scan using idx_pdb_v5 on PointTbl_v5 (cost=0.00
[postgis-users] RE : PostGIS 2.0 Raster Mosaic...
I agree you Nicolas ! Are you sure the projection of your raster is SRID 2273 ? Regarding the first error, it seems to me you have a projection problem (but I am not really familiar with this kind of "extent" error) Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Brian Fitzgerald Date: mar. 11/09/2012 19:10 À: PostGIS Users Discussion Objet : Re: [postgis-users] PostGIS 2.0 Raster Mosaic... I just tried the following (following your example) and get the following error: ./raster2pgsql -d -s 2273 -t 256x256 -C -F /home/rcgeoadmin/temp/979801.tif public.testmosaic > /home/rcgeoadmin/temp/979801mosaic.sql psql -d mydb -f 979801mosaic.sql ./raster2pgsql -a -s 2273 -t 256x256 -M -F /home/rcgeoadmin/temp/979903.tif public.testmosaic > /home/rcgeoadmin/temp/979903mosaic.sql psql -d mydb -f 979903mosaic.sql ERROR: postgres@minty /home/rcgeoadmin/temp $ psql -d mydb -f 979903mosaic.sql BEGIN psql:979903mosaic.sql:2: ERROR: new row for relation "testmosaic" violates check constraint "enforce_max_extent_rast" psql:979903mosaic.sql:3: ERROR: current transaction is aborted, commands ignored until end of transaction block psql:979903mosaic.sql:4: ERROR: current transaction is aborted, commands ignored until end of transaction block . On Tue, Sep 11, 2012 at 12:57 PM, Markus Innerebner wrote: I am doing the same thing with success: use my script: cheers Markus * param 1: name the directory in which the files are located param2: name of the table in which the files are loaded * more raster2pgHydroalp.sh #!/bin/bash # this script imports raster ascii file into postgis # isFirst=true; cd $1; for i in $(\ls *.asc); do echo $i if $isFirst then raster2pgsql -d -s 32632 -C -F $i $2 | sudo su - postgres -c 'psql -d hydroalp'; isFirst=false else raster2pgsql -a -s 32632 -M -F $i $2 | sudo su - postgres -c 'psql -d hydroalp' fi done cd .. *** -- Ph D. Student Markus Innerebner DIS Research Group - Faculty of Computer Science Free University Bozen-Bolzano Dominikanerplatz 3 - Room 211 I - 39100 Bozen Phone: +39-0471-016143 Mobile: +39-333-9392929 gpg --fingerprint - pub 1024D/588F6308 2007-01-09 Key fingerprint = 6948 947E CBD2 89FD E773 E863 914F EB1B 588F 6308 sub 2048g/BF4877D0 2007-01-09 On Sep 11, 2012, at 6:43 PM, Brian Fitzgerald wrote: 979903mosaic.sql ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- -- Brian Fitzgerald, PMP, GISP bkf...@gmail.com 803.426.3469 --- <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
I think your query does not use the index on net_geom because it is not a
spatial index (not a gist one) on the geometry, but a btree index on the id.
You should make the index (create index net_geom_gist on net_geom using
gist(geom) ) and try again your query
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Thomas
Klemmer
Envoyé : mardi 11 septembre 2012 11:09
À : postgis-users@postgis.refractions.net
Objet : [postgis-users] ST_DWithin is not using SpatialIndex with Subquery
Hi folks
first of here some system informations:
Server: Ubuntu 12.04 LTS, 16GB RAM 1TB 4x SSD HW Raid
"PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro
4.6.3-1ubuntu5) 4.6.3, 64-bit"
"POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September
2009" GDAL="GDAL 1.9.1, released 2012/05/15" LIBXML="2.7.8" LIBJSON="UNKNOWN"
TOPOLOGY RASTER"
Database:
TableName: PointTbl_v1
Index: idx_pdb_v1
NumRows:4.09806e+08
TableSize: 280 GB
IndexSize: 21 GB[Gist on(point_pos)]
GeometryCo:point_pos
Type:POINT
Dimensions: 2
SRID: 4326
/---
TableName:PointTbl_v5
Index: idx_pdb_v5
NumRows: 4.16218e+07
TableSize: 19 GB
IndexSize: 2344 MB [Gist on(point_pos)]
Primarykey:false
GeometryCo: point_pos
Type: POINT
Dimensions:2
SRID: 4326
/---
TableName:NetTbl
Index:idx_net
NumRows: 270615
TableSize: 195 MB
IndexSize: 17 MB
Primarykey: NetTbl_pk
GeometryCo:net_geom
Type:LINESTRING
Dimensions: 2
SRID: 4326
Basically I'm trying to gather all points from PointTbl_v5 / PointTbl_v1 that
are close or on a LineString in NetTbl;
Here ist the Query I'm trying to run:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, ..., type
FROM PointTbl_v5
WHERE ST_DWithin(point_pos,(SELECT net_geom from NetTbl where ogc_fid = 500)
,5e-05);
This query takes 319005 msec to return which is very slow due to the a seq scan
done on PoinDB_v5
EXPLAIN ANALYSE
"Seq Scan on PointTbl_v5 (cost=108.36..10013364820.01 rows=13873927
width=202) (actual time=199926.978..318895.494 rows=5 loops=1)"
" Filter: st_dwithin(point_pos, $0, 5e-05::double precision)"
" InitPlan 1 (returns $0)"
"-> Index Scan using NetTbl_pk on NetTbl (cost=0.00..8.36 rows=1
width=847) (actual time=2.069..2.075 rows=1 loops=1)"
" Index Cond: (ogc_fid = 2)"
"Total runtime: 318895.583 ms"
The query:
SELECT ST_AsText(net_geom) from NetTbl where ogc_fid = 2
returns in 16 ms;
If I instert the Geometry by Hand into the first query like this:
SELECT ST_AsBinary(point_pos) AS point_pos, oid, country, federalstate,
district, town
FROM PointTbl_v5
WHERE ST_DWithin(point_pos, ST_GeomFromText( 'LINESTRING()',4326),5e-05);
This query return in 63ms on the small table and 766ms on the bigger table.
EXPLAIN ANALYSE
"Index Scan using idx_pdb_v5 on PointTbl_v5 (cost=0.00..147.61 rows=1
width=202) (actual time=0.047..1.050 rows=23 loops=1)"
" Index Cond: (point_pos && '0103.A40'::geometry)"
" Filter: (('0102.4A40'::geometry && st_expand(point_pos, 5e-05::double
precision)) AND _st_dwithin(point_pos, '01020.A40'::geometry, 5e-05::double
precision))"
"Total runtime: 1.080 ms"
Does anybody have a clue why the first query with the subquery for the
LineString Geometry withing ST_DWithin not using the Spatial index?
seqscan_enabled is turned off...
cheers Thomas
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : PostGIS 2.0 Raster Mosaic...
I may be wrong but I think the problem comes from the index. You use the option -I to create it but you cannot create it more than once. You should just remove the -I option from the second time you use your command and I think it should work. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Brian Fitzgerald Date: lun. 10/09/2012 17:57 À: PostGIS Users Discussion Objet : [postgis-users] PostGIS 2.0 Raster Mosaic... ... trying to create a raster mosaic using the raster2pgsql tool... I imported a tif image and now I want to append a second adjacent tiff to the first one... figured I could just run the same command with -F -I -t 256x256 etc and tack on the -a? I tried this and got an error saying: 'testmosaic_raster_gist' already exists... anyone know how to accomplish this? - Brian -- -- Brian Fitzgerald, PMP, GISP bkf...@gmail.com 803.426.3469 --- <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Retrieving linestring data between endpoints
Hello, Maybe you could simply try to merge every segment and make a dump of this unique multiline to keep only the ones which are simple lines. Select st_dump(st_union(geom)) from mytable Hugues. -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Max Pyziur Envoyé : mercredi 5 septembre 2012 19:16 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Retrieving linestring data between endpoints Greetings, I'm trying to figure out a way of constructing linestrings between endpoints. Shapefile data for pipelines and railroads does not present continuous lines; instead they are in the form of small line segments. The applications are using data for roads, pipelines, or railroads. Interstate 70 is not one complete linestring between Washington DC and the middle of Utah; instead it is a series of segments. The same with pipeline and railroad data. The problem becomes more cumbersome (at least to me) when there is no knowledge of the data between two points (say Edmonton and Vancouver) and you want to see if there is a way to string a set of segments between two points (to answer a question like is there a railline between Edmonton and Vancouver)? I would appreciate any advice on how to proceed. Much thanks, Max Pyziur p...@brama.com ___ 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] change column type in view
Didi you simply try to change type on the fly ? Something like should work
st_estimated_extent('public', geometry_columns.f_table_name,
geometry_columns.f_geometry_column)::geometry AS extent
Hugues.
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Dale Rudrum
Envoyé : mercredi 29 août 2012 15:55
À : postgis-users@postgis.refractions.net
Objet : [postgis-users] change column type in view
To have an easy and quick acces to the various projects we have had over the
years I would like to have a table with in it polygons representing the areas
in which work was done. It seems to me that a view would be easiest as this is
updated automatically. I came up with this script:
CREATE OR REPLACE VIEW project_overview AS
SELECT geometry_columns.f_table_name AS table,
geometry_columns.f_geometry_column AS geometrycol,
st_estimated_extent('public', geometry_columns.f_table_name,
geometry_columns.f_geometry_column) AS extent
FROM geometry_columns
WHERE geometry_columns.f_table_name::text <> 'project_overview';
which works fine, save that the type of the column extent is not geometry.
Therefore autocad and quantum GIS cannot acces the information
Is there a way to change this in the definition of the view, or to bypass the
problem by using a second view? As a newby I'm stuck at this point.
Also suggestions for different setups solving my problem would be appreciated.
Because of our use of autocad we are restricted to PostGIS 1.5 on postgres 9.0.
Thanks
Dale Rudrum
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : Bounding Box intersection
Coordinates of a rectangle, you mean of thé points at the corners ? You should try st_makepolygon(st_makeline()) or st_makeenvelope. Postgis manual is very useful, you should also try it to learn how these function work and which one is tthe better one for you Hugues - Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date:7/2012 14 À: PostGIS Users Discussion Objet : [postgis-users] Bounding Box intersection Hi All, I have a table with linestring geometries, and I have the co-ordinates of a rectangle/ bounding box. I want to get only a restricted set of geometries that lie within or intersect with this box. Is there a way to construct this box on the fly and get only the relevant set of geometries? Cheers,o Ed <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Are views slow ing down my wms?
Hello, It will take sometimes to retrieve the set of data from the view relative to its complexity. Is the request which define the view simple or does it have complex and / or spatial operator ? Does your spatial data have a lot of points ? Did you make indexes on the columns used by your views ? Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Robert Buckley Envoyé : jeudi 12 juillet 2012 07:30 À : Post GIS Users Discussion Objet : [postgis-users] Are views slow ing down my wms? Hi, I have created a database structure which has 3 main postgis tables (1 for lines,polygones, and points) which each contain between 5-10,000 features. These tables then feed views which split the geometries into understandable groups which are then served through geoserver in Tomcat6, linux-Ubuntu 10.04. I have noticed that the performance of the wms is getting considerably worse. Is this to do with the views or simply the amount of data being served? Should I in hindsight have split the table into separate tables instead of creating views? thanks for any help, Robert ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Coordinate transformation with custom records ofspatial_sys_ref
Hello,
I think you use wrong srid : for lambert93 you should use 2154 and for lambert
2 carto it is 27582.
Anyway, if you want to use the SRID 320002120 and 310024140, your problem is
related to the geometry_column view and constraints checking on geometry
columns. You will have to use AddGeometryColumm with use_typmod option set to
false.
http://postgis.refractions.net/docs/AddGeometryColumn.html said : "Changed:
2.0.0 If you require the old behavior of constraints use the default
use_typmod, but set it to false."
Hugues.
-Message d'origine-
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de DUPREZ
Cédric
Envoyé : vendredi 29 juin 2012 15:46
À : postgis-users@postgis.refractions.net
Objet : [postgis-users] Coordinate transformation with custom records
ofspatial_sys_ref
Hi all list members,
I am facing a problem with coordinate transformation, using ST_Transform()
function.
First of all, my PostGIS version is 2.0.1 : POSTGIS="2.0.1 r9979"
GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.1,
released 2012/05/15" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY RASTER
Here are the steps to reproduce the problem:
1/ Create a table containing points
CREATE TABLE pts (
code INT,
xl2 INT,
yl2 INT,
geom GEOMETRY,
CONSTRAINT pts_pkey PRIMARY KEY (code)
)
WITHOUT oids;
INSERT INTO pts (code, xl2, yl2)
VALUES (1, 556866, 1902345)
, (2, 689174, 2448195)
, (3, 729095, 2528577)
, (4, 335755, 2059702)
, (5, 623207, 2210468)
, (6, 850337, 1871486)
, (7, 915275, 1889534)
, (8, 639551, 1778327)
, (9, 584365, 2273523)
, (10, 649599, 2228833);
2/ Import the custom records of spatial_sys_ref specified by the French IGN.
The file is provided here:
http://lambert93.ign.fr/fileadmin/files/IGNF-spatial_ref_sys.sql
3/ Populate the geom column:
UPDATE pts
SET geom = ST_SetSRID(ST_MakePoint(xl2, yl2), 320002120);
I get the following notices: NOTICE: SRID value 320002120 > SRID_MAXIMUM
converted to 999442
SELECT DISTINCT ST_SRID(geom)
FROM pts;
Gives: 999442
But SELECT * FROM geometry_columns;
Gives: 0
4/ Update geometry SRID:
SELECT UpdateGeometrySRID('pts', 'geom', 320002120);
SELECT * FROM geometry_columns;
Gives: 999442
5/ Try the transformation from 320002120 SRID (Lambert II) to 310024140 SRID
(Lambert 93):
SELECT code, ST_Transform(geom, 310024140)
FROM pts;
I get the following error message: Invalid reserved SRID (999442).
With PostGIS 1.5.3, this worked perfectly, without any error message and gave
the good coordinate transformation.
What should I change with PostGIS 2.0.1 in order to get the same result again?
Thanks in advance for your help.
Regards,
Cedric Duprez
___
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] Raster data insertion in postgis database
Hello, I'm sorry but I don't understand your problem because I don't see what exactly is your data : - files you have to import to postgis using raster2psql ? (you may use -a option within an existing table and *.ext to import all of ones in one time) - different postgis tables with tiles for each one ? (you may use create a new table unioning your tables from two select * ) -something else ? Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de gautam kumar Envoyé : jeudi 28 juin 2012 12:47 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Raster data insertion in postgis database Hi, I am using multiple threads to upload the raster tiles simultaneously in a single table in postgis database. 2500 rows should be created from first thread and 2450 rows from the second thread..giving a total of 4950 rows. What i am getting is around 3006 rows. I am wondering if it is possible to insert multiple rows simultaneously in a single table in postgis database. Please help Regards, Gautam ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Simplifying Polygons
It preserves topology for a simple polygon but not for an entire layer Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de robert_cl...@doh.state.fl.us Envoyé : mardi 26 juin 2012 14:45 À : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] Simplifying Polygons Try ST_SimplifyPreserveTopology. -Rob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Steve Horn Sent: Tuesday, June 26, 2012 8:39 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Simplifying Polygons Hello! Having a a client-side performance problem due to a large number of polygons being brought down to a Google Map. Sometimes the GZipped response is around 2MB worth of GeoJSON. I'm hoping that I can partially solve the problem by simplifying the polygons using ST_Simplify. The problem I'm running into is that the polygons are all contiguous and after simplification they are not connecting with each other. (See attached image) Anyone know how I can simplify the polygons and keep them all "fitting together"? -- Steve Horn ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Simplifying Polygons
Hello, This is an ordinary problem and there are tow solution to solve it : - a plpg function wrote by nicolas ribot : http://trac.osgeo.org/postgis/wiki/UsersWikiSimplifyPreserveTopology - a function wrote by sandro santilli using the new topology functions of postgis 2 : http://strk.keybit.net/blog/2012/04/13/simplifying-a-map-layer-using-postgis-topology/ And also a related approach by Nicolas ribot : http://trac.osgeo.org/postgis/wiki/UsersWikiSimplifyWithTopologyExt With all this stuff, you should manage to do what you want Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Steve Horn Envoyé : mardi 26 juin 2012 14:39 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Simplifying Polygons Hello! Having a a client-side performance problem due to a large number of polygons being brought down to a Google Map. Sometimes the GZipped response is around 2MB worth of GeoJSON. I'm hoping that I can partially solve the problem by simplifying the polygons using ST_Simplify. The problem I'm running into is that the polygons are all contiguous and after simplification they are not connecting with each other. (See attached image) Anyone know how I can simplify the polygons and keep them all "fitting together"? -- Steve Horn ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Selecting pairs of points that have the samedistance between them
Hello,
I may be wrong but I don't see any answer to your question. Below is a request
which could do the job you need if I understood it well :
Hugues
PS : as you can see Nicolas, I learnt from your mail and I'm now able to create
a "serial" column into a request ;)
create temp sequence myseq increment by 1 Minvalue 1;
with mydist as (
select nextval('myseq') new_id, a.id id1, b.id id2, st_distance(a.geom, b.geom)
dist from my table_of_points a, my table_of_points b
where a.id < b.id
)
select a.new_id n_id1, b.new_id n_id2, id1, id2 from mydist
where a.new_id < b.new_id
and a.dist = b.dist
De : postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] De la part de Bob Pawley
Envoyé : dimanche 24 juin 2012 20:16
À : postgis-users@postgis.refractions.net
Objet : [postgis-users] Selecting pairs of points that have the samedistance
between them
Hi
I have a geometry of points (more than one pair) that represent the
intersections between a line and a column of multistrings.
Can anyone point me to a method of determining which pairs of points are the
same distance apart as are other pairs of points.
This will isolate the closed object from the open object for me.
The srid is all -1.
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : 3D polyline simplify
I don't understand what is a 3D polyline. Your 3D points have x, y coordinates and a z value. If you make your polyline from a selection of points in a raster DEM, you will just have a polyline representing something from the 3D. With st_simplifiy, you will symplify this representation. Instead your polyline is a multiline, I think you can use st_simplify instead of st_simplifypreservetopology. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date: dim. 17/06/2012 11:30 À: PostGIS Users Discussion Objet : [postgis-users] 3D polyline simplify Does *ST_SimplifyPreserveTopology : *work with a 3D polyline as well? So if I had a linestring which I composed from several 3D points using ST_MakeLine, would the DP algorithm still work? Cheers, Ed* * <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : Linestring intersections + intersection order
Hello, I think you could order your points using st_distance. Something like this request should work : select st_distance(st_startpoint(a.the_geom), st_intersection(a.the_geom, b.the_geom)), st_intersection(a.the_geom, b.the_geom) the_geom from my_single_linestring a, my_table_of_linestrings b where st_intersects (a.the_geom, b.the_geom) order by st_distance(st_startpoint(a.the_geom), st_intersection(a.the_geom, b.the_geom)) I don't know how to have a simple id (1, 2, 3, ..., n) without creating a new table with serial column or using a plpg function with a loop. Hugues Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Ed Linde Date: ven. 15/06/2012 22:44 À: PostGIS Users Discussion Objet : [postgis-users] Linestring intersections + intersection order Hi all, I am trying to compute the intersection between a given linestring and a whole set of linestrings in a table. So the idea is that this single linestring will get intersected by the lines in the table at multiple points. Only thing is that i know the start and end of the linestring and I would like to know the ORDER in which these intersections took place. So the end result is that I get to see my query linestring along with all the "intersection points" with an increasing ID to indicate which point comes before the other. Hope that made sense. :) Cheers, Ed <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : How does ST_Slope works ?
Hello, Thanks for your answer. If some functions can't handle all the pixels, this is a big limit for the use of tiled rasters if some function can't handle all the pixels although tiling and indexing raster is a major improvement (thanks to postgis).It's going to be tricky if we need to have both tiled and untiled version for the same raster. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: mer. 13/06/2012 21:13 À: PostGIS Users Discussion Objet : Re: [postgis-users] How does ST_Slope works ? > 1. To know which Pixeltype I had to use, I tried to use st_bandpixeltype which > told me my pixels were 32BSI type but my results using this type are 0 or 1 > and if > I try another type like 32BF, results seems to be more useful. Does someone > have an explanation about this weird difference ? What is the unit used by > st_slope ? The results are in radian so rounded to 0 or 1 when the pixel type is integer. > 2. My raster is a tiled one and as you can see on both pictures, ST_Slope > gives no > results for pixels located at the border of each tile. I can understand it is > impossible to find the slope for the pixels which make the border of the > raster, > but is there not a way to have complete results for the tile in the middle of > the > raster and thus use the neighboring tiles. Not yet. The only way is to load the raster untiled or to ST_Union them which might be slow. Pierre ___ 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] Diff. Between 2 tables
You could also try with not exists select * from table2 where not exists (select unique_key from table1 where table1.unique_key = table2.unique_key) Hugues. De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Lee Hachadoorian Envoyé : jeudi 7 juin 2012 16:31 À : postgis-users@postgis.refractions.net Objet : Re: [postgis-users] Diff. Between 2 tables On 06/07/2012 09:14 AM, Hemin Tofiq wrote: Thank you, I have 2 tables with the same structure, so we don't need to create alias for the same table, however that is fine with updated rows, how can I get newly created rows? Regards, Hemin I assume you mean you have added a new row to one table but not the "twin" table. Comparing on a unique key would look like: SELECT * FROM table1 LEFT JOIN table2 ON (table1.unique_key = table2.unique_key) WHERE table2.unique_key IS NULL; I think you said you want to be able to do this on geometries. I haven't tried this, but I assume something like this might work: SELECT * FROM table1 LEFT JOIN table2 ON (ST_Equals(table1.the_geom, table2.the_geom)) WHERE table2.the_geom IS NULL; Just make sure to read the docs on ST_Equals vs. ST_OrderingEquals so that you know which comparison you want. http://postgis.refractions.net/documentation/manual-1.5/ST_Equals.html http://postgis.refractions.net/documentation/manual-1.5/ST_OrderingEquals.html Best, --Lee -- Lee Hachadoorian PhD, Earth & Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Diff. Between 2 tables
Hello, I'm not sure I understand the whole thing but in a basic way and if your table have a primary key a self join should help you : select a.* from mytable a, mytable b where a.id = b.id and (a.attr1 != b.attr1 or a.attr2 != b.attr2) The attribut can be a geometry or anything else. Hugues -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Hemin Tofiq Envoyé : jeudi 7 juin 2012 09:16 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Diff. Between 2 tables Hi, I would like to ask if there is a way to compare (find the difference) between 2 tables? Updated geometries or attributes), i.e. An sql som returnes the rows that are updated. Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/Diff-Between-2-tables-tp4998062.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ 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] RE : AddRasterColumn support
Maybe you should try something like : http://postgis.refractions.net/docs/RT_AddRasterConstraints.html Hugues http://postgis.refractions.net/docs/PostGIS_Special_Functions_Index.html#NewFunctions_2_0 : In PostGIS 2.0, raster type and raster functionality has been integrated. There are way too many new raster functions to list here and all are new so please refer to Chapter 9, Raster Reference for more details of the raster functions available. Earlier pre-2.0 versions had raster_columns/raster_overviews as real tables. These were changed to views before release. Functions such as ST_AddRasterColumn were removed and replaced with AddRasterConstraints, DropRasterConstraints as a result some apps that created raster tables may need changing. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Yogesh Dahiya Date: mer. 06/06/2012 14:53 À: postgis-users@postgis.refractions.net Objet : [postgis-users] AddRasterColumn support I am working on somebody else postgis project and he used addrastercolumn but postgis does not have reference to that function. And that is because wkt raster support has been abandoned by postgis. So what alternatives do I have for providing raster support. <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : RE : get unique values from raster intersecting apolygon
Thanks but i think the way I made my picture was not really clear and I understand now how you read it ! I already know the existence of st_value for points. Here, the red point on my picture are the values of the raster and not a table of points : if it is build from a DEM, you have the elevation measure based on a regular pattern and finally you get a grid and in the centre of each cell the value measured (my red points) For my problem I have two table : one made of polygons, the other a raster made of tiles. I want to get value where polygons intersect raster but a celle must not appear twice. My raster will be totally covered by polygons and at the end I want a gid|val table with as many lines as my raster has values. I hope it is clearer now but, for sure, my english does not help ! Thanks, Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 16:56 À: PostGIS Users Discussion Objet : Re: [postgis-users] RE : get unique values from raster intersecting apolygon I this case the right query looks like this: SELECT gid, ST_Value(rast, geom) val FROM yourraster table, yourpointtable WHERE ST_Intersects(rast, geom) Make sure to tile and index your raster properly (smaller is better). You can do this at import time. The result for cell outside any polygon will be "null". Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Francois Hugues > Sent: Monday, May 28, 2012 9:06 AM > To: PostGIS Users Discussion > Subject: [postgis-users] RE : get unique values from raster intersecting > apolygon > > I want one value per red point (which are raster data). > > The gid of one polygon will appear as many times as necessary regarding how it > intersects the raster. > > Hugues. > > > Message d'origine > De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine > Date: lun. 28/05/2012 14:24 > À: PostGIS Users Discussion > Objet : Re: [postgis-users] get unique values from raster intersecting > apolygon > > You want one value per polygon or one value per red point? It is not clear to > me > from your drawing. > > Pierre > > > -Original Message- > > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > > boun...@postgis.refractions.net] On Behalf Of Francois Hugues > > Sent: Saturday, May 26, 2012 5:29 PM > > To: postgis-users@postgis.refractions.net > > Subject: [postgis-users] get unique values from raster intersecting a > > polygon > > > > Hello list, > > > > I have to get values from raster where they intersect polygons from a layer > with > > numerous polygons which have shared boundaries, but I don't want to have > > duplicate values from raster. The final result must be something like : gid > > | val. > > > > To explain my case, I made a picture. > > > > I think ST_intersection could help but I don't know what will be the exact > result. > > In my example, we have a grid (a DEM for example), each red point is the > > value > > in the centre of each cell and two polygons A and B. I think cells totally > > within > > polygons are not a problem, but in green I have highlighted some ambiguous > > cases : > > - 1 and 2 are cells shared by both polygons but not in the same proportions > > and > I > > would like to get value 1 for polygon A but not for B and value 2 for > > polygon B > > but not for A > > - What will be the result for cell number 3 and for the other green cell > > without > > number (and for all the ones which are not totally within a polygon) ? I > > think > the > > value is the same for the whole cell (st_dumpaspolygon) and will be returned > > whatever the part of the cell covered by the polygon, but if someone could > > confirm. > > > > If st_intersection use st_DumpAsPolygon, could it be better to work directly > with > > this polygonized table using its own gist index and dealing with shared > > areas of > > cells between different polygons ? > > > > Hugues. > > > > > > > > > > ___ > 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] RE : get unique values from raster intersecting apolygon
I want one value per red point (which are raster data). The gid of one polygon will appear as many times as necessary regarding how it intersects the raster. Hugues. Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Pierre Racine Date: lun. 28/05/2012 14:24 À: PostGIS Users Discussion Objet : Re: [postgis-users] get unique values from raster intersecting apolygon You want one value per polygon or one value per red point? It is not clear to me from your drawing. Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Francois Hugues > Sent: Saturday, May 26, 2012 5:29 PM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] get unique values from raster intersecting a polygon > > Hello list, > > I have to get values from raster where they intersect polygons from a layer > with > numerous polygons which have shared boundaries, but I don't want to have > duplicate values from raster. The final result must be something like : gid | > val. > > To explain my case, I made a picture. > > I think ST_intersection could help but I don't know what will be the exact > result. > In my example, we have a grid (a DEM for example), each red point is the value > in the centre of each cell and two polygons A and B. I think cells totally > within > polygons are not a problem, but in green I have highlighted some ambiguous > cases : > - 1 and 2 are cells shared by both polygons but not in the same proportions > and I > would like to get value 1 for polygon A but not for B and value 2 for polygon > B > but not for A > - What will be the result for cell number 3 and for the other green cell > without > number (and for all the ones which are not totally within a polygon) ? I > think the > value is the same for the whole cell (st_dumpaspolygon) and will be returned > whatever the part of the cell covered by the polygon, but if someone could > confirm. > > If st_intersection use st_DumpAsPolygon, could it be better to work directly > with > this polygonized table using its own gist index and dealing with shared areas > of > cells between different polygons ? > > Hugues. > > > > ___ 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] RE : st_offsetcurve
Ok, the error comes from the select : try using select a.n_street,
my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
I forgot to add the table reference "a."
I think I made another mistake using the over method. It would rather be
something like that :
select n_street, st_union(ST_Parallel(the_geom,3.0,0,0)) over(partition by
n_street) my_union_geom,
ST_Parallel(the_geom,-3.0,0,0)) my_single_geom
from streets
Firts time I read too fast this time I wrote too fast !
Hugues.
Message d'origine
De: postgis-users-boun...@postgis.refractions.net de la part de Pedro Costa
Date: lun. 14/05/2012 17:36
À: PostGIS Users Discussion
Objet : Re: [postgis-users] st_offsetcurve
Thank you Francois but still error.
Using this:
With my_union as (
select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) my_union_geom
from streets
group by n_street)
select n_street, my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
from my_union a
join streets b on a.n_street = b.n_street
give me this error:
column reference "n_street" is ambiguous
I put the table name but give me another error:
invalid reference to FROM-clause entry for table "my_union"
Em 11-05-2012 11:01, Francois Hugues escreveu:
> Hi,
>
> This error is normal. You can not use a group by clause with an aggregate
> function and keep all of single records from your original database. Which
> one will you choose to keep for each unique n-street value ?
>
> To do that you can use two different tricks (but your table will have as many
> lines as you have unique couple n_street/the_geom):
>
> Using "with" with a join
> With my_union as (
> select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) my_union_geom
> from streets
> group by n_street)
>
> select n_street, my_union_geom, ST_Parallel(the_geom,-3.0,0,0) my_single_geom
> from my_union a
> join streets b on a.n_street = b.n_street
>
> or using an over function (not totally sure of this particular request but
> the window function can certainly help you in this case)
>
> select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) ,
> ST_Parallel(the_geom,-3.0,0,0))over(partition by n_street)
> from streets
> group by n_street
>
> Hugues.
>
> --
>
> -Message d'origine-
> De : postgis-users-boun...@postgis.refractions.net
> [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pedro
> Costa
> Envoyé : vendredi 11 mai 2012 10:30
> À : PostGIS Users Discussion
> Objet : Re: [postgis-users] st_offsetcurve
>
> Ups, sorry...
>
> The whole sql:
>
> create table test4 as
> (select n_street, st_union (ST_Parallel(the_geom,3.0,0,0)) ,
> (ST_Parallel(the_geom,-3.0,0,0))
> from streets
> group by n_street)
>
>
>
> Em 11-05-2012 00:03, Simon Greener escreveu:
>> Pedro,
>>
>>> greate. The funcion works.
>>>
>>> I'm doing something like that:
>>>
>>> ST_Parallel(the_geom,3.0,0,0)
>>>
>>> but give me error:
>>>
>>> column "streetss.the_geom" must appear in the GROUP BY clause or be used
>>> in an aggregate function
>>>
>>> anybody know the problem?
>> Not without the whole SQL statement.
>>
>> S
>>> thanks
>>>
>>>
>>> Em 10-05-2012 04:40, Simon Greener escreveu:
>>>> Folks,
>>>>
>>>> Sorry about that. The file:
>>>>
>>>> http://www.spatialdbadvisor.com/file_download/55/PostGIS_Parallel.sql
>>>>
>>>> Did not contain all the required types and functions. One needed also
>>>> to access the types and functions for my ST_GetVector function.
>>>>
>>>> I have now included all the required types and related functions in
>>>> the above script and replaced it on my site.
>>>>
>>>> I have changed a bit in the code as it didn't handle some situations
>>>> as I expected. Hopefully my test cases give good coverage.
>>>>
>>>> My tests, conducted on PostgreSQL 9.1 and PostGIS 2.0 on Windows 7 64
>>>> Bit all work as this example shows:
>>>>
>>>> With geometries As (
>>>>select ST_GeomFromText('LINESTRING(1 1,1 10)') as geom,
>>>> 10.0 as offset,2 as roundFactor,0 as curved
>>>> union all select ST_GeomFromText('LINESTRING(0 0,1 1,1 2)') as geom,
>>>>0.5 as offset,2 as roundFactor,
>>>> generate_series(0,1,1) as curved
>>>> union a
Re: [postgis-users] st_offsetcurve
;> 14.14,135 59.14,190 4.14,190 -55,40.86 -55,-7.07 -7.07)" >>> "LINESTRING(0 0,45 45,90 0,135 45,180 0,180 -45,45 -45,0 >>> 0)";10.0;1;"COMPOUNDCURVE((7.07 -7.07,45 30.86,82.93 >>> -7.07),CIRCULARSTRING(82.93 -7.07,90 -10,97.07 -7.07),(97.07 >>> -7.07,134.71 31.15,170 -4.14,170 -35,49.14 -35,7.07 >>> 7.07))";"COMPOUNDCURVE((-7.07 7.07,37.93 52.07),CIRCULARSTRING(37.93 >>> 52.07,45 55,52.07 52.07),(52.07 52.07,89.71 13.85,127.93 >>> 52.07),CIRCULARSTRING(127.93 52.07,135 55,142.07 52.07),(142.07 >>> 52.07,187.07 7.07),CIRCULARSTRING(187.07 7.07,189.16 4.01,190 0),(190 >>> 0,190 -45),CIRCULARSTRING(190 -45,187.13 -52.01,180 -55),(180 -55,45 >>> -55),CIRCULARSTRING(45 -55,41.27 -54.28,37.93 -52.07),(37.93 >>> -52.07,-7.07 -7.07))" >>> "MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 >>> 4))";0.5;0;"MULTILINESTRING((1.65 2.65,3 1.29,5.35 >>> 3.65))";"MULTILINESTRING((2.35 3.35,3 2.71,4.65 4.35))" >>> "MULTILINESTRING((0 0,1 1,1 2),(2 3,3 2,5 4))";0.5;1;"MULTICURVE((1.65 >>> 2.65,2.65 1.65),CIRCULARSTRING(2.65 1.65,3 1.5,3.35 1.65),(3.35 >>> 1.65,5.35 3.65))";"MULTILINESTRING((2.35 3.35,3 2.71,4.65 4.35))" >>> >>> Sorry for any inconvenience. >>> >>> The file is available for download via the link above. >>> >>> regards >>> Simon >>> >>> >>> On Wed, 09 May 2012 23:27:04 +1000, Pedro Costa >>> wrote: >>> >>>> No problem Hugues. >>>> >>>> Give me the same error: >>>> >>>> ERROR: function st_parallel(geometry, numeric, numeric, integer) does >>>> not exist >>>> LINE 1: select ST_Parallel(the_geom, 3.0, 0.0, 0) as test from tests >>>> >>>> >>>> >>>> Em 09-05-2012 13:48, Francois Hugues escreveu: >>>>> Sorry, I read your message too fast and forgot the PS. >>>>> >>>>> Maybe you could try something like : >>>>> select ST_Parallel(the_geom, 3.0, 0.0, 0) as test from tests >>>>> >>>>> Hugues. >>>>> >>>>> >>>>> >>>>> -Message d'origine- >>>>> De : postgis-users-boun...@postgis.refractions.net >>>>> [mailto:postgis-users-boun...@postgis.refractions.net] De la part de >>>>> Pedro Costa >>>>> Envoyé : mercredi 9 mai 2012 13:06 >>>>> À : PostGIS Users Discussion >>>>> Objet : Re: [postgis-users] st_offsetcurve >>>>> >>>>> Em 09-05-2012 11:28, Pedro Costa escreveu: >>>>>> PS: I´m already install the function by your file >>>>> I'm already do that... >>>>> ___ >>>>> 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 >> > > ___ 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] RE : return points where line intersects polys
Hello,
I'm not totally sure of that but you could try this way:
1. Keep points from st_intersection
2. Extract points from lines using st_startpoint / endpoint (and add an
attribut to identify which kind of point it is )
3. Dump your geometrycollection and re-do the two first steps to get just points
4. Make an union of all these results to have just one table
Hugues
Message d'origine
De: postgis-users-boun...@postgis.refractions.net de la part de Puneet Kishor
Date: jeu. 10/05/2012 17:21
À: PostGIS Users Discussion
Objet : Re: [postgis-users] return points where line intersects polys
On May 7, 2012, at 3:44 PM, Stephen Woodbridge wrote:
> Hi Puneet,
>
> First if you intersect a line with a polygon you should expect to get back:
>
> 1. a line if it crosses the polygon or lies alont an edge of it
> 2. or a point if it touches a vertex
> 3. or a collection of these if there are multiple instances along the line
>
> The results are always returned in a GEOMETRYCOLLECTION.
>
> Also you should expect to get one result row for every row in TABLE
>
> If you only want, results for rows the intersect then add a WHERE clause
>
> SELECT ST_AsText(ST_Intersection(ST_GeomFromText('LINESTRING(,)',
> 4326), the_geom))
> FROM table
> WHERE ST_Intersects(ST_GeomFromText('LINESTRING(,)', 4326), the_geom);
>
>
Thanks... the above works well. But, now I am confused by a slightly different
problem. How do I guarantee that the returned results are in the same order as
the direction of the line? For example, given the line below ( .. ),
how do I guarantee that I will get back "a, b" and not "b, a"?
Suggestions?
> -Steve
>
> On 5/7/2012 1:36 PM, Puneet Kishor wrote:
>> Given a line, I want to get the points at which it intersects a polygon
>> dataset.
>>
>> +--+
>> | |
>> ++ |
>> |a | b |
>> oo==o===
>> p0 |p1 |p2|p3 pn
>> ++ |
>> | |
>> | |
>> +--+
>>
>> In the figure above, I want to get the following
>>
>> poly_id point1 point2
>> --- -- --
>> a p1 p2
>> b p2 p3
>>
>> SELECT
>> ST_AsText(ST_Intersection(ST_GeomFromText('LINESTRING(,)', 4326),
>> the_geom))
>> FROM table;
>>
>> But I back N 'GEOMETRYCOLLECTION EMPTY' where N = number of rows in the
>> 'table'. From what I understand, that indicates "the geometries do not share
>> any space (are disjoint)". But, I know that is not correct. So, my query is
>> formulated incorrectly. What am I doing wrong?
>>
___
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_offsetcurve
Sorry, I read your message too fast and forgot the PS. Maybe you could try something like : select ST_Parallel(the_geom, 3.0, 0.0, 0) as test from tests Hugues. -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pedro Costa Envoyé : mercredi 9 mai 2012 13:06 À : PostGIS Users Discussion Objet : Re: [postgis-users] st_offsetcurve Em 09-05-2012 11:28, Pedro Costa escreveu: > PS: I´m already install the function by your file I'm already do that... ___ 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_offsetcurve
Hi, That's because has not been implemented by default in postgis : you have to run the script which simon gave you at the end of his article. This wil create the necessary functions http://www.spatialdbadvisor.com/file_download/55/PostGIS_Parallel.sql Hugues. -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pedro Costa Envoyé : mercredi 9 mai 2012 12:28 À : PostGIS Users Discussion Objet : Re: [postgis-users] st_offsetcurve Hi Simon, I had already seen its function. Congratulations. But it returns me the following error: ERROR: function st_parallel(geometry, integer, integer, integer) does not exist LINE 1: select (ST_Parallel(the_geom, 3, 0, 0)) as test ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. when i do this: select (ST_Parallel(the_geom, 3, 0, 0)) as test from tests do you know the problem? PS: I´m already install the function by your file Em 09-05-2012 11:22, Simon Greener escreveu: > You could try my ST_Parallel function that works with all geometry > based versions of PostGIS. > > http://www.spatialdbadvisor.com/postgis_tips_tricks/205/st_parallel-for-postgis > > > > NOTE: Projected based math only. > > regards > S > On Wed, 09 May 2012 20:15:41 +1000, Pedro Costa > wrote: > >> Em 09-05-2012 10:46, Francois Hugues escreveu: >>> If i'm not wrong, this function really dos not exists in postgis >>> 1.5.3 and is avalaible since 2.0. >> >> Thanks. >> Anybody know a way of transform a polygon buffer in lines? >> >> >> ___ >> 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] st_offsetcurve
Hello, If i'm not wrong, this function really dos not exists in postgis 1.5.3 and is avalaible since 2.0. http://postgis.refractions.net/docs/ST_OffsetCurve.html Hugues. -- Hugues FRANÇOIS -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Pedro Costa Envoyé : mercredi 9 mai 2012 11:40 À : PostGIS Users Discussion Objet : [postgis-users] st_offsetcurve Hi, I'm trying to use the st_offsetcurve like this: UPDATE test4 SET the_geom = ST_Multi( ST_Union( (ST_OffsetCurve(the_geom, 3.0, 'quad_segs=4 join=round')), (ST_OffsetCurve(the_geom, -3.0, 'quad_segs=4 join=round'; But gives me this error: ERROR: function st_offsetcurve(geometry, numeric, unknown) does not exist LINE 5: (ST_OffsetCurve(the_geom, 3.0, 'quad_segs=4 join=round')), ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I have the geos 3.3.1 and postgis 1.5.3. He tried to change the value of the distance because I saw in some forums but the error seems to be the last parameters. Does anyone know how to solve? 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] Shp2pgsql-gui, Save to schema other than public?
Yes you could use alter table but I meant you should click on public, which is the default value, into the gui and set it to whatever you want. Hug -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Ryan Dalton Envoyé : mardi 17 avril 2012 17:30 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Shp2pgsql-gui, Save to schema other than public? I see that I can run the command "ALTER TABLE [name] SET SCHEMA [new_schema]" via SQL, is this what you are referring to? Or are you suggesting an alternate way to "manually edit the schema" within the GUI that I must not be seeing...? Ryan Did you try to edit manually the name of the shema ? That should be ok and you can do the same to set the table name, geometry column and srid. Hug I just got PostGIS 2.0 installed and was using the Shp2pgsql-gui to load some shapefiles into the database. I have created a 'data' schema where I was planning to store all of my imported data, but when I use the shapefile import GUI, it does not appear as though there is an option to specify which schema to load the data into, and it just goes into 'public'. Is it possible to load to an alternate schema, and am I just missing something obvious? Maybe a future feature enhancement if not? Thanks, Ryan ___ 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] Shp2pgsql-gui, Save to schema other than public?
Did you try to edit manually the name of the shema ? That should be ok and you can do the same to set the table name, geometry column and srid. Hug De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de Ryan Dalton Envoyé : mardi 17 avril 2012 06:25 À : postgis-users@postgis.refractions.net Objet : [postgis-users] Shp2pgsql-gui, Save to schema other than public? I just got PostGIS 2.0 installed and was using the Shp2pgsql-gui to load some shapefiles into the database. I have created a 'data' schema where I was planning to store all of my imported data, but when I use the shapefile import GUI, it does not appear as though there is an option to specify which schema to load the data into, and it just goes into 'public'. Is it possible to load to an alternate schema, and am I just missing something obvious? Maybe a future feature enhancement if not? Thanks, Ryan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : raster2psql - reprojection issue
I don't think raster2pgsql can be use to reproject anything : it could be better to import the raster into postgis using its original projection and then use postgis to reproject to the wanted srid using st_transform (http://www.postgis.org/documentation/manual-svn/RT_ST_Transform.html) Hug Message d'origine De: postgis-users-boun...@postgis.refractions.net de la part de Mark Wynter Date: sam. 14/04/2012 08:37 À: postgis-users@postgis.refractions.net Objet : [postgis-users] raster2psql - reprojection issue This may be a basic issue, but I'm on a steep learning curve with respect to raster analysis and PostGIS 2.0. I have a large land use raster file ( w001001.adf ) projected in EPSG:3577 - GDA94 / Australian Albers. In QGIS when opening this file, I can do an "on the fly" CRS transformation to SRID 4326 and all is good. When importing this file into PostGIS, is it possible to transform this file into a geographic coordinate system, and if so, how? I've loaded the data into PostGIS using: raster2pgsql -s 4326 -I -C -M w001001.adf -F -t 5000x5000 landuse_50m | psql -U postgres -d test However, when I then view the resultant PostGIS table in OPENJUMP, the data remains in the projected coordinate system rather than the geographic coordinate system. i.e. upper left = -1888000.000,-101.000, not decimal degrees. Details of w001001.adf are as follows: gdalinfo w001001.adf file Driver: AIG/Arc/Info Binary Grid Size is 80200, 76740 Coordinate System is: PROJCS["unnamed", GEOGCS["GDA94", DATUM["Geocentric_Datum_of_Australia_1994", SPHEROID["GRS 1980",6378137,298.257222101, AUTHORITY["EPSG","7019"]], TOWGS84[0,0,0,0,0,0,0], AUTHORITY["EPSG","6283"]], PRIMEM["Greenwich",0, AUTHORITY["EPSG","8901"]], UNIT["degree",0.0174532925199433, AUTHORITY["EPSG","9122"]], AUTHORITY["EPSG","4283"]], PROJECTION["Albers_Conic_Equal_Area"], PARAMETER["standard_parallel_1",-18], PARAMETER["standard_parallel_2",-36], PARAMETER["latitude_of_center",0], PARAMETER["longitude_of_center",132], PARAMETER["false_easting",0], PARAMETER["false_northing",0], UNIT["METERS",1]] Origin = (-1888000.000,-101.000) Pixel Size = (50.000,-50.000) Corner Coordinates: Upper Left (-1888000.000,-101.000) (115d23'34.35"E, 8d29'49.17"S) Lower Left (-1888000.000,-4847000.000) (109d29'30.81"E, 42d49'11.28"S) Upper Right ( 2122000.000,-101.000) (150d38'16.50"E, 8d11'43.92"S) Lower Right ( 2122000.000,-4847000.000) (157d13'46.69"E, 42d25' 0.67"S) Center ( 117000.000,-2928500.000) (133d11'36.60"E, 27d 0'36.35"S) Band 1 Block=256x4 Type=Int16, ColorInterp=Undefined Min=100.000 Max=663.000 NoData Value=-32768 Is there something I'm not doing, or understanding? Could someone please help me out? Kind regards, Mark ___ 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] How to design a database for continents, countries, regions, cities and POIs?
I think it could be better to have a table for each kind of object because they will have different attributes (for an example, in a matter of scale, a POI belong to a city, a city belong to a province which is part of a sub_region which is included into a state and the continent is composed of several states). Hugues -Message d'origine- De : postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] De la part de mkubenka Envoyé : lundi 9 avril 2012 13:31 À : postgis-users@postgis.refractions.net Objet : [postgis-users] How to design a database for continents, countries, regions, cities and POIs? I'm brand new to GIS programming and I am designing a GIS application. Target is to create system with continents, countries, regions (including states, sub-regions, provinces), cities and places in cities. Each of this elements will contain some text information and related stuff. As database we are going to use PostgreSQL with PostGIS. My question is how to design database for this system? I was thinking of 2 tables polygons and points, but I'm not sure if it's good way of thinking. -- View this message in context: http://postgis.17.n6.nabble.com/How-to-design-a-database-for-continents-countries-regions-cities-and-POIs-tp4715669p4715669.html Sent from the PostGIS - User mailing list archive at Nabble.com. ___ 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] osm2pgsql schema and table name
Hello user list, A little question not directly related to postgis but i'm afraid about making a mistake which could results in data loss when i will use create option in osm2pgsql. I did not find the way to specify the schema and table name i would like to use to import data. Does something like osm2pgsql -c schema.tablename could work ? Else which are the default parameters of importation ? Public schema ? File name as table name ? I hope i'm not disturbing to much the mailing list, Thanks in advance for your answers, Hug <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] RE : Using ST_intersection on Polygons returns multiple types
Maybe you could also try to extract polygons to a new table polygons only
with intersection as (
SELECT st_intersection(the_geom, (select the_geom from B)) as the_geom
FROM A
WHERE st_intersects(the_geom, (select the_geom from B))
AND ST_GeometryType(the_geom) = 'ST_Polygon'
)
select * from intersection
where st_geometrytype(the_geom) in ('ST_Polygon', 'ST_MultiPolygon')
I may be wrong but i think shape file can mix polygons and multipolygons
Hug
Message d'origine
De: postgis-users-boun...@postgis.refractions.net de la part de Paul Ramsey
Date: mer. 07/03/2012 19:08
À: PostGIS Users Discussion
Objet : Re: [postgis-users] Using ST_intersection on Polygons returns multiple
types
ST_Intersection is perhaps a bit more complete than most users would
like. If two polygons share a boundary what is their intersection? Not
a polygon, but that linear, shared boundary: a line. Similar for
touching at a point.
Anyhow, probably a cleaner approach would be an extra optional
argument to ST_Intersection allowing users to specify the
dimensionally of the return, but in the meanwhile you can go to
ST_CollectionExtract and use that to pull out just the polygons.
P.
On Wed, Mar 7, 2012 at 9:58 AM, Rebecca Clarke wrote:
> Hi there
>
> I have 2000 polygons in table A and I want to show only the polygons that
> intersect with the bounding box specified in table B . So I do this:
>
> SELECT the_geom
> FROM A
> WHERE st_intersects(the_geom, (select the_geom from B))
> AND ST_GeometryType(the_geom) = 'ST_Polygon'
>
> This query returns all the intersecting polygons. When I run st_geometrytype
> they are all St_Polygon.
>
> Some of the polygons are large and exceed the boxes boundary so I use this
> query to trim them:
>
> SELECT st_intersection(the_geom, (select the_geom from B)) as the_geom
> FROM A
> WHERE st_intersects(the_geom, (select the_geom from B))
> AND ST_GeometryType(the_geom) = 'ST_Polygon'
>
>
> The problem I'm having is that when I use the st_intersection, instead of
> returning just St_Polygon's, it's returning other types as well:
>
> ST_LineString
> ST_MultiLineString
> ST_MultiPolygon
> ST_Polygon
>
> I need to output the results to a SHP file and this is preventing me from
> doing so seeing there is more than one Geom Type.
>
> Any ideas how I can keep the results of the st_intersection as just
> St_Polygons? Am I doing something wrong?
>
> Regards
>
> Rebecca
>
>
>
>
>
> ___
> 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
