Re: [postgis-users] ST_Within and GEOMETRYCOLLECTION
The problem case is where a line does not lie completely in either component, but does potentially lie in the union of the components. See the attached image for a simple case. Essentially this requires computing whether three line segments intersect in a point or which side of an intersection point a segment lies, both of which requires high-precision computation. Trying to handle this would substantially complicate the current JTS algorithm, which is one reason it wasn't done originally. Also, there's robustness issues both with trying to compute the correct answer with limited precision, and with overlaying polygons in general. But it's definitely annoying that GCs are not handled by the predicates. The intersects predicate as least could be fairly easily extended to handle GCs. On 5/7/2012 12:33 PM, Paul Ramsey wrote: On Mon, May 7, 2012 at 4:41 PM, Martin Davis wrote: It's because the underlying JTS/GEOS operation doesn't support GeometryCollections. And this in turn is because of the details of the implementation - in particular, handling the situation where two polygons overlap. It would require computing the overlay of the polygons in order to detect the within condition at the point of overlap. Why so? If you're in one component, does that end the test? The answer is 'true'. P. <>___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Very large WHERE clause?
As an alternative, you could use PL/pgSQL to create a custom function, and then pass one (or more) arguments to the query's "WHERE" clause dynamically. At the application level, this would allow users for example to from a form, where the select are populated using the results of a SELECT DISTINCT query. -- View this message in context: http://postgis.17.n6.nabble.com/Very-large-WHERE-clause-tp4952906p4959028.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
Re: [postgis-users] return points where line intersects polys
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);
As far as the points you want, you should probably intersect the
polygon's outer ring with your line, or extract the points from the
start and end of the line. You also have to deal with the case where the
line lies along an edge.
-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?
--
Puneet Kishor
___
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_Within and GEOMETRYCOLLECTION
On Mon, May 7, 2012 at 4:41 PM, Martin Davis wrote: > It's because the underlying JTS/GEOS operation doesn't support > GeometryCollections. And this in turn is because of the details of the > implementation - in particular, handling the situation where two polygons > overlap. It would require computing the overlay of the polygons in order to > detect the within condition at the point of overlap. Why so? If you're in one component, does that end the test? The answer is 'true'. P. > > > On 5/7/2012 7:52 AM, Hemin Tofiq wrote: >> >> Hi, >> The documentation about ST_Within says: >> "Do not call with a GEOMETRYCOLLECTION as an argument", without saying >> why. >> I can not finde more Info about taht, du anyone has an explanation? >> >> http://postgis.refractions.net/docs/ST_Within.html >> >> Thanks in advance, >> Hemin >> >> -- >> View this message in context: >> http://postgis.17.n6.nabble.com/ST-Within-and-GEOMETRYCOLLECTION-tp4957949.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 >> >> >> - >> No virus found in this message. >> Checked by AVG - www.avg.com >> Version: 2012.0.1913 / Virus Database: 2425/4983 - Release Date: 05/07/12 >> >> > ___ > 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_Within and GEOMETRYCOLLECTION
Thank you. Regards, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/ST-Within-and-GEOMETRYCOLLECTION-tp4957949p4958438.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] return points where line intersects polys
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?
--
Puneet Kishor
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Very large WHERE clause?
It is all one table (20M+ records). But it has 16 columns. The main filter (WHERE) is the bbox column (a geometry column). But there are other columns such as image type, producer, and time that we also want to allow the user to use to downselect. And as I said, between the type and the producer there are about 40 possible values. I also suspect that it might be wise to break the query into two. First doing the spatial selection (which should bring the table down to a few thousand entries) and then do a subsequent search on the type, producer, date, etc. Is this true, or is the PLANNER smart enough to do this? (Like I said, I a C.S. software guy, not a DB guy). -- View this message in context: http://postgis.17.n6.nabble.com/Very-large-WHERE-clause-tp4952906p4958314.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
Re: [postgis-users] Very large WHERE clause?
Keep it simple! Did you try joining tables? regards ronald Am 07.05.2012 16:26, schrieb DrYSG: Sounds good to me. the four columns that are being searched (producer, creator, distributor, type) each have about 10 different values, and are indexed as a B-Tree. But the size of the table (and there is only one table is 20M records) so it will be interesting to see the results. I did get the following idea from someone in a different list, but he has the same issue I have, I am more a software person than a DB person, so I suspect that indexing the bitfield value may lead to performance issues: --- Not as a DB guy, but as a software guy, I might implement something along these lines: Create a bit array for each of the attribute, of sufficient length. Then assign one bit for each value. Assuming this is an image search system, and you want to search for PNG, JPG and GIF images, create a 3-bit array, with bit 0 turned on means include PNG in search, bit 1 turned on means include JPG in search and bit 2 turned on means include GIF In search. So, based on what the user selects, you will get a 3-bit number for image format. Similarly, create n-bit numbers for each of the other attributes. At the end, concatenate all of them to get one large N-bit number. Also, at the time of storing an image, I would populate such an N-bit number for each image, based on the attributes that are set for that particular image. Now, my query will have just one WHERE clause which would look like: WHERE bitfield_stored_in_db& bitfield_from_search_form<> 0; -- View this message in context: http://postgis.17.n6.nabble.com/Very-large-WHERE-clause-tp4952906p4957891.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
Re: [postgis-users] ST_Within and GEOMETRYCOLLECTION
It's because the underlying JTS/GEOS operation doesn't support GeometryCollections. And this in turn is because of the details of the implementation - in particular, handling the situation where two polygons overlap. It would require computing the overlay of the polygons in order to detect the within condition at the point of overlap. On 5/7/2012 7:52 AM, Hemin Tofiq wrote: Hi, The documentation about ST_Within says: "Do not call with a GEOMETRYCOLLECTION as an argument", without saying why. I can not finde more Info about taht, du anyone has an explanation? http://postgis.refractions.net/docs/ST_Within.html Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/ST-Within-and-GEOMETRYCOLLECTION-tp4957949.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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2012.0.1913 / Virus Database: 2425/4983 - Release Date: 05/07/12 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Within and GEOMETRYCOLLECTION
Hi, The documentation about ST_Within says: "Do not call with a GEOMETRYCOLLECTION as an argument", without saying why. I can not finde more Info about taht, du anyone has an explanation? http://postgis.refractions.net/docs/ST_Within.html Thanks in advance, Hemin -- View this message in context: http://postgis.17.n6.nabble.com/ST-Within-and-GEOMETRYCOLLECTION-tp4957949.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
Re: [postgis-users] calculate profile on raster
> I need to calculate a raster profile along a linestring. > The only way I've found is using ST_Intersection(rast,geom) but it seems to > provide poor perfomances (even with ST_Intersects), probably because of the > rast->geom conversion. > Is there a better way to do it? > > My query is: > > select st_intersection(rast,the_geom) from dem,profile where st_intersects > (rast,the_geom) > I have the gist index both on rast and the_geom. Did you tile your raster? Smaller is better... Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Storing image file metadata in PostGIS
Billy, The raster2pgsql loader has a -R option to load rasters as out-db file. In this case a RASTER column is created storing only the bounding box of the raster and the location of the file in the file system. You can then use most of the PostGIS raster function to extract metadata or interact with geometries (like ST_Intersecst(rast, geom)). ST_BandPath(rast) will get you the path to the raster file. I think most functions accessing the pixel value now works seamlessly but they should be slower than if the complete raster (with pixel values) would be stored in the DB. Pierre > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Billy Newman > Sent: Saturday, May 05, 2012 4:32 PM > To: postgis-users@postgis.refractions.net > Subject: [postgis-users] Storing image file metadata in PostGIS > > Hello all, > > I am currently using a pain old Oracle (not Oracle Spatial) database to store > metadata for a huge amount of imagery data. I store this metadata for file > discovery purposes. I.E. when a request is made for imagery I can quickly > hit the > DB to get all overlapping image files. I can then grab those off the file > system > and serve them out. > > I am using imageio-ext (Java) that sits on top of gdal to pull the imagery's > metadata. Then just sending that to the DB. > > I am probably doing this the hard way and wondering if PostGIS can help > simplify > things. > > I am wondering if PostGIS has support to store metadata for images, and what > that support really gives me. Currently I am serving out images in only > EPSG:4326 so I want to do any transformations (if the image is not in > EPSG:4326) > to the metadata before storing it in the database. > > Can PostGIS read an image file and extract the metadata, transform the > metadata into an EPSG:4326 lat/lon bounding box and store it? > > This is my current data model in Oracle if that helps: > > DataSource (this contains general info about this group of image files) > > ImageFile ( each data source has 0 - N image files) > double minLat > double maxLat > double minLon > double maxLon > > Its as simple as that. Although I am doing quite a bit of work using > imageio-ext, > and gdal to try and extract, transform, and store the metadata. Is this less > complex in PostGIS or essentially the same? > > Thanks! > Billy ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Very large WHERE clause?
Sounds good to me. the four columns that are being searched (producer, creator, distributor, type) each have about 10 different values, and are indexed as a B-Tree. But the size of the table (and there is only one table is 20M records) so it will be interesting to see the results. I did get the following idea from someone in a different list, but he has the same issue I have, I am more a software person than a DB person, so I suspect that indexing the bitfield value may lead to performance issues: --- Not as a DB guy, but as a software guy, I might implement something along these lines: Create a bit array for each of the attribute, of sufficient length. Then assign one bit for each value. Assuming this is an image search system, and you want to search for PNG, JPG and GIF images, create a 3-bit array, with bit 0 turned on means include PNG in search, bit 1 turned on means include JPG in search and bit 2 turned on means include GIF In search. So, based on what the user selects, you will get a 3-bit number for image format. Similarly, create n-bit numbers for each of the other attributes. At the end, concatenate all of them to get one large N-bit number. Also, at the time of storing an image, I would populate such an N-bit number for each image, based on the attributes that are set for that particular image. Now, my query will have just one WHERE clause which would look like: WHERE bitfield_stored_in_db & bitfield_from_search_form <> 0; -- View this message in context: http://postgis.17.n6.nabble.com/Very-large-WHERE-clause-tp4952906p4957891.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
Re: [postgis-users] Do I have to be a superuser to use postgisextensions?
It might have to do with search_paths. the CREATE EXTENSION postgis_topology adds topology to the search path for you, but this is lost if you create from template. _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Donovan Cameron Sent: Sunday, May 06, 2012 3:37 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Do I have to be a superuser to use postgisextensions? Been playing further and found that the only way I can create a database for another user is not from the template_postgis because any user loses access to the topology schema for some reason but from a scratch database. This is even when I create the new database from the postgres user with: createdb -T template_postgis -O saultdon newdb #user saultdon can not access topology schema psql newdb -c 'GRANT USAGE ON SCHEMA topology TO PUBLIC;' #user saultdon can not access topology schema dropdb newdb Below is what works. As postgres user: createdb newdb -O saultdon psql newdb -c 'CREATE EXTENSION postgis;' psql newdb -c 'CREATE EXTENSION postgis_topology;' psql newdb -c 'GRANT USAGE ON SCHEMA topology to PUBLIC;' That is the only way I can get a postgis enabled database for a non-postgres user. Even though the GRANT USAGE has been used on template_postgis and my user saultdon can access the template_postgis and use 'SELECT postgis_full_version();' on it successfully, the user saultdon can create a new database from template_postgis but will lose access to the topology schema within the new db... Maybe this is a bug of some sort but not sure where to point it out. Or is there something I can suggest to the package maintainers for openSUSE to set some defaults like: 1.) create a template_postgis database by default a.) GRANT USAGE ON SCHEMA topology TO PUBLIC; and, b.) update pg_database set datistemplate = true where datname = 'template_postgis'; Donovan On Sun, May 6, 2012 at 11:58 AM, Donovan Cameron wrote: I also read from some instructions for something unrelated [1] that this function also works, as postgres user: su - postgres psql update pg_database set datistemplate = true where datname = 'template_postgis'; \q exit That looks like it is setting a configuration parameter in the pg_database listing that flags the template_postgis database as a template allowing it to be used in the same manner as template1. So I read further and found it on pg 1675 or Table 45-15. pg_database Columns in the 9.1 Postgresql manual. This seems alot more appropriate than to use ALTER or GRANT which are irrelevant for what I am trying to do. So I tried it and it now allows non-postgres users to use template_postgis as a template in case any other openSUSE users need to know. But now the weird part... Any new databases I created from template_postgis doesn't have topology enabled again! But my first database does for some reason. The first database that is working was created from the postgres user that simply set the flag '-O saultdon' for the createdb command. The second database is created from the user saultdon from the template_postgis because I can copy it now. So I tried again to create the database as postgres: postgres:~> createdb -O saultdon -T template_postgis newdb Again, only postgres user can use topolgy schema and user saultdon cannot! I don't really understand what's happening because now the saultdon user can't use 'CREATE EXTENSION postgis;' on any databases created using just: createdb newdb where a template is not used. Gives me the same - must be superuser - error. This is even after I've run the 'GRANT USAGE ON SCHEMA topology TO PUBLIC;' And restarted the postgresql server. Donovan [1] http://code.grical.org/browser/INSTALL.TXT?rev=417%3A8ccbd40c0aba#L169 On Sun, May 6, 2012 at 11:30 AM, Donovan Cameron wrote: Using the GRANT sql worked. I read more about it in the postgresql manual. Thanks. GRANT USAGE ON SCHEMA topology TO PUBLIC; Then It never fully took effect till I restarted the server, su - rcpostgresql restart exit Done. Now, that part about giving access to PUBLIC on the template_postgis database. This is because without it, a non-superuser cannot create a database of their own when using: createdb -T template_postgis newdb createdb: database creation failed: ERROR: permission denied to copy database "template_postgis" I tried using the similar GRANT for databases but it doesn't mention anything about allowing users to copy. Only connect, so I tried that: GRANT CONNECT ON DATABASE template_postgis TO PUBLIC; But still, cannot create a database without being postgres user. Not sure if I need to maybe make user of ALTER like so: ALTER DATABASE template_postgis OWNER TO PUBLIC; Or try, ALTER ROLE saultdon CREATEDB IN DATABASE template_postgis; Does one of those give me copy permissions because CONNECT hasn't. Thanks for the help so far with changing schema privileges. Donovan On
[postgis-users] calculate profile on raster
I need to calculate a raster profile along a linestring. The only way I've found is using ST_Intersection(rast,geom) but it seems to provide poor perfomances (even with ST_Intersects), probably because of the rast->geom conversion. Is there a better way to do it? My query is: > select st_intersection(rast,the_geom) from dem,profile where st_intersects (rast,the_geom) I have the gist index both on rast and the_geom. giovanni ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Does dump/restore lose CLUSTER?
Hi, if I have a database clustered on a GIST index, and subsequently transmit it to another machine using pg_dump/pg_restore, will I have to repeat the CLUSTER command, or is the dump/restore process likely to use the "on disk order" so that the resulting database will be more or less clustered even without? This is for a 500 GB table of linestring geometries on PostGIS 2.0/PostgreSQL 9.1. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09" E008°23'33" ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Do I have to be a superuser to use postgis extensions?
On Mon, May 07, 2012 at 09:14:32AM +0200, Sandro Santilli wrote: > On Sun, May 06, 2012 at 12:36:35PM -0700, Donovan Cameron wrote: > > > Even though the GRANT USAGE has been used on template_postgis and my user > > saultdon can access the template_postgis and use 'SELECT > > postgis_full_version();' on it successfully, the user saultdon can create a > > new database from template_postgis but will lose access to the topology > > schema within the new db... > > I can't confirm, after trying with PostgreSQL 8.4.10. > Can you report the error message you get when trying to use topology within > the new db ? The only problem I see is with search_path not retaining the > value of the template. Oh, another thing: I installed postgis using the old school scripts, not the EXTENSION model (which may do things differently). --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Do I have to be a superuser to use postgis extensions?
On Sun, May 06, 2012 at 12:36:35PM -0700, Donovan Cameron wrote: > Even though the GRANT USAGE has been used on template_postgis and my user > saultdon can access the template_postgis and use 'SELECT > postgis_full_version();' on it successfully, the user saultdon can create a > new database from template_postgis but will lose access to the topology > schema within the new db... I can't confirm, after trying with PostgreSQL 8.4.10. Can you report the error message you get when trying to use topology within the new db ? The only problem I see is with search_path not retaining the value of the template. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net - http://vizzuality.com `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
