[postgis-users] Spatio-Temporal Function
Hi, I am writing some spatio-temporal function in postgis. Like, ST_Enters( obj_geom, boundary_geom, time t1,time t2) i.e. Does vehicle enters in ABC area between time t1 to t2? For example, select ST_Enters ( obj_geom, (select boundary_geom from boundary), '25/2/2011 12:23:32','26/2/2011') from vehicledata where vehicleid= 'XYZ'; Vehicledata is a table consist of more than 10,000 object location geometry named obj_geom. On query execution, it take one object geometry at every execution of ST_Enters and compare with boundary area. But this doesn't provide proper output because it is continous function of time i.e. Enters is made up of Disjoint -> Touches -> Inside.First, check for disjoint, if it is true at t1, then check for touches at t2 then check for inside at t3, provided t1http://old.nabble.com/Spatio-Temporal-Function-tp31056304p31056304.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] Raster2pgsql.py Possible No Data Problem
Hi. Sorry. I was confused and used the Python 2.5. Now i get some other errors when using python 2.6. Could it be my setup which is not correct? C:\Python26>python raster2pgsql.py -r c:\prio_grid\source\mountain\mean_mnt_bin. tif -t mountain -s 4326 -I -o mountain.sql Traceback (most recent call last): File "raster2pgsql.py", line 1036, in main() File "raster2pgsql.py", line 999, in main gt = wkblify_raster(opts, filename.replace( '\\', '/') , i, gt) File "raster2pgsql.py", line 941, in wkblify_raster summary = wkblify_raster_level(options, ds, options.overview_level, band_ran ge, infile, i) File "raster2pgsql.py", line 901, in wkblify_raster_level hexwkb += wkblify_band(options, band, level, xoff, yoff, read_block_size, bl ock_size, infile, b) File "raster2pgsql.py", line 790, in wkblify_band target_block_size[0], target_block_size[1]) File "C:\Python26\Lib\site-packages\osgeo\gdal.py", line 978, in ReadAsArray import gdalnumeric File "C:\Python26\Lib\site-packages\osgeo\gdalnumeric.py", line 1, in from gdal_array import * File "C:\Python26\Lib\site-packages\osgeo\gdal_array.py", line 24, in _gdal_array = swig_import_helper() File "C:\Python26\Lib\site-packages\osgeo\gdal_array.py", line 23, in swig_imp ort_helper return _mod UnboundLocalError: local variable '_mod' referenced before assignment C:\Python26> 2011/3/2 Jorge Arévalo > On Wed, Mar 2, 2011 at 5:40 PM, Andreas Forø Tollefsen > wrote: > > Hi, > > Postgresql 8.4.7 > > postgis-pg84-binaries-2.0.0svn.zip > > Tested with latest raster2pgsql.py rev 6860 > > I have a raster which is giving me problems when creating the raster > table. > > The raster does not have any digits for no data but 'no data'. > > Could this be the cause of the below problem? I tried a different raster > > which normally have been working with the old gdal2wktraster.py script. > > Any suggestions? > > The error: > > C:\Python25>python raster2pgsql.py -r > > c:\prio_grid\source\mountain\mean_mnt_bin. > > tif -t mountain -o mountain.sql > > Traceback (most recent call last): > > File "raster2pgsql.py", line 1036, in > > main() > > File "raster2pgsql.py", line 999, in main > > gt = wkblify_raster(opts, filename.replace( '\\', '/') , i, gt) > > File "raster2pgsql.py", line 941, in wkblify_raster > > summary = wkblify_raster_level(options, ds, options.overview_level, > > band_ran > > ge, infile, i) > > File "raster2pgsql.py", line 853, in wkblify_raster_level > > nodata_values = collect_nodata_values(ds, band_from, band_to) > > File "raster2pgsql.py", line 532, in collect_nodata_values > > if nodata is not None and not math.isnan(nodata): > > AttributeError: 'module' object has no attribute 'isnan' > > C:\Python25> > > > > ___ > > postgis-users mailing list > > postgis-users@postgis.refractions.net > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > Hi, > > The problem is with the 'isnan' function. I see it was included in > Python 2.6. We need a replacement in case of lower Python versions. > I'll fix it asap (hopefully, when I arrive home). > > Best regards, and thanks for the feedback > > -- > Jorge Arévalo > Internet & Mobilty Division, DEIMOS > jorge.arev...@deimos-space.com > http://es.linkedin.com/in/jorgearevalo80 > http://mobility.grupodeimos.com/ > http://gis4free.wordpress.com > http://geohash.org/ezjqgrgzz0g > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] mapear campo de tipo "geometry" con hibernate?
gracias por la respuesta. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] mapear campo de tipo "geometry" con hibernate?
On Wed, 2011-03-02 at 11:25 -0800, joel ml wrote: > tengo esta duda, ya que estoy utilizando hibernate, para el mapeado de > la base de datos, y no se como mapear este tipo de campo (geometry), > por favor quiero una ayuda, si pudieran explicarme estaria agradecido, > gracias de antemano!. > Has probado usando hibernate spatial? http://www.hibernatespatial.org/ -- Fabio R. Panettieri Lead Software Engineer http://www.xoomcode.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] mapear campo de tipo "geometry" con hibernate?
Hi, look here: http://www.hibernatespatial.org/ Fred 2011/3/2 joel ml > tengo esta duda, ya que estoy utilizando hibernate, para el mapeado de la > base de datos, y no se como mapear este tipo de campo (geometry), por favor > quiero una ayuda, si pudieran explicarme estaria agradecido, gracias de > antemano!. > > > > ___ > 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] mapear campo de tipo "geometry" con hibernate?
tengo esta duda, ya que estoy utilizando hibernate, para el mapeado de la base de datos, y no se como mapear este tipo de campo (geometry), por favor quiero una ayuda, si pudieran explicarme estaria agradecido, gracias de antemano!. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Raster2pgsql.py Possible No Data Problem
On Wed, Mar 2, 2011 at 5:40 PM, Andreas Forø Tollefsen wrote: > Hi, > Postgresql 8.4.7 > postgis-pg84-binaries-2.0.0svn.zip > Tested with latest raster2pgsql.py rev 6860 > I have a raster which is giving me problems when creating the raster table. > The raster does not have any digits for no data but 'no data'. > Could this be the cause of the below problem? I tried a different raster > which normally have been working with the old gdal2wktraster.py script. > Any suggestions? > The error: > C:\Python25>python raster2pgsql.py -r > c:\prio_grid\source\mountain\mean_mnt_bin. > tif -t mountain -o mountain.sql > Traceback (most recent call last): > File "raster2pgsql.py", line 1036, in > main() > File "raster2pgsql.py", line 999, in main > gt = wkblify_raster(opts, filename.replace( '\\', '/') , i, gt) > File "raster2pgsql.py", line 941, in wkblify_raster > summary = wkblify_raster_level(options, ds, options.overview_level, > band_ran > ge, infile, i) > File "raster2pgsql.py", line 853, in wkblify_raster_level > nodata_values = collect_nodata_values(ds, band_from, band_to) > File "raster2pgsql.py", line 532, in collect_nodata_values > if nodata is not None and not math.isnan(nodata): > AttributeError: 'module' object has no attribute 'isnan' > C:\Python25> > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users > > Hi, The problem is with the 'isnan' function. I see it was included in Python 2.6. We need a replacement in case of lower Python versions. I'll fix it asap (hopefully, when I arrive home). Best regards, and thanks for the feedback -- Jorge Arévalo Internet & Mobilty Division, DEIMOS jorge.arev...@deimos-space.com http://es.linkedin.com/in/jorgearevalo80 http://mobility.grupodeimos.com/ http://gis4free.wordpress.com http://geohash.org/ezjqgrgzz0g ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 10:42:17 AM, Mark Cave-Ayland wrote: > On 02/03/11 16:30, Karl O. Pinc wrote: > > I'm unfamiliar with geography_columns. What is the security > > model? Can anyone do anything like with geography_columns? > > > > If it's a view then would it be possible to put triggers > > on the underlying tables? Where would I look in the code? > > It's still under discussion, but the view is generated automatically > by > querying the system catalogues to pull out the spatial columns (i.e. > it > can't be altered, but users will be able to see other users' spatial > columns in there). If you wanted I suppose you could have such a view check permissions and thereby avoid revealing such information to unauthorized users. Just a thought. > > This means that AddGeographyColumn() and DropGeometryColumn() can > just > > add/remove the column from the table directly, so this can only > happen > > on tables for which the current role has SQL permissions. I get it now. They're "real" data types. Great. That's really what I want. Thanks for all the help. If I decide to go ahead and write some triggers I'll post the results to the wiki. Seeing as how there's plans in the works to address the problem I probably won't do anything at all unless a problem arises at my end. Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 02/03/11 16:30, Karl O. Pinc wrote: Humm. Ok. I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? Thanks. It's still under discussion, but the view is generated automatically by querying the system catalogues to pull out the spatial columns (i.e. it can't be altered, but users will be able to see other users' spatial columns in there). This means that AddGeographyColumn() and DropGeometryColumn() can just add/remove the column from the table directly, so this can only happen on tables for which the current role has SQL permissions. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Raster2pgsql.py Possible No Data Problem
Hi, Postgresql 8.4.7 postgis-pg84-binaries-2.0.0svn.zip Tested with latest raster2pgsql.py rev 6860 I have a raster which is giving me problems when creating the raster table. The raster does not have any digits for no data but 'no data'. Could this be the cause of the below problem? I tried a different raster which normally have been working with the old gdal2wktraster.py script. Any suggestions? The error: C:\Python25>python raster2pgsql.py -r c:\prio_grid\source\mountain\mean_mnt_bin. tif -t mountain -o mountain.sql Traceback (most recent call last): File "raster2pgsql.py", line 1036, in main() File "raster2pgsql.py", line 999, in main gt = wkblify_raster(opts, filename.replace( '\\', '/') , i, gt) File "raster2pgsql.py", line 941, in wkblify_raster summary = wkblify_raster_level(options, ds, options.overview_level, band_ran ge, infile, i) File "raster2pgsql.py", line 853, in wkblify_raster_level nodata_values = collect_nodata_values(ds, band_from, band_to) File "raster2pgsql.py", line 532, in collect_nodata_values if nodata is not None and not math.isnan(nodata): AttributeError: 'module' object has no attribute 'isnan' C:\Python25> ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 09:55:57 AM, Mark Cave-Ayland wrote: > On 02/03/11 15:17, Karl O. Pinc wrote: > > > Yes, I've been thinking more about this and have come to > > the same conclusion you have. The right way to go is > > to put triggers on geometry_columns that check permissions > > against what's granted on the column holding the geometry > > data. > > > > Any clue if this would be something I could send in > > as a patch to the postgis project? > > Probably not, as I believe Paul is planning to switch the binary > representation of the geometry type in 2.0 which would then enable us > to > make geometry_columns a view just like geography_columns. > > However, it would probably be of interest to enough people to add it > somewhere to the user part of the wiki. Humm. Ok. I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? Thanks. Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Value from Polygon
>Ok thanks. I got a bit confused regarding the vectorizing. Isnt that what i am >doing with this query? >I have now reduced the resolution to increase performance. Each cell is >now 0.02224x0.02224. (previous X 8) Yes this is what you are doing. ST_Intersection will polygonize (or vectorize) every tile before intersecting. What I meant is that it SHOULD be used in conjonction with ST_Intersects in the WHERE clause on a tiled coverage. This is what I would call the "vector" way of computing raster statistics. I'm working on a "pixel" way where we count pixel one after the other without vectorizing anything. The performance of the "vector" way is better for a certain tile size but it seems not to reduce with smaller tile. The "pixel" way does get better performance with smaller tiles so that I get much better performance now with this method than with the vector one. I had to reimport my coverage with -k25x25 instead of 100x100 though. The pixel method is a bit more coarse since it is a "take all the pixel or nothing" approach when the vector method "cuts" the pixels so that you get exactly the right proportion intersecting your polygon. It will be interesting to compare the two approaches. I will try to add the "pixel" method to the wiki soon. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 02/03/11 15:17, Karl O. Pinc wrote: Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Probably not, as I believe Paul is planning to switch the binary representation of the geometry type in 2.0 which would then enable us to make geometry_columns a view just like geography_columns. However, it would probably be of interest to enough people to add it somewhere to the user part of the wiki. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 03:11:20 AM, Maria Arias de Reyna wrote: > El Tuesday 01 March 2011, Karl O. Pinc escribió: > > Hi, > > > > I have a number of users each of which has their > > own schema. I don't want the users to be able > > to enable/disable the geospatialness of anyone > > else's columns. What's the right way to > > secure postgis so as to prevent this? It seems > > that a single, global, geometry_columns is the > > problem. > What if you write a trigger on every delete/update on the > geometry_columns > table? This trigger can cancel the delete/update if the user has no > "permission" for that row. Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Karl Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speed of query
On 01/03/11 21:48, Michael Smedberg wrote: OK, next guess. It looks like your SQL might be pretty inefficient. For example, consider this SQL statement: SELECT a, b FROM ll WHERE ST_Within( ST_Point( ST_X(ST_Transform(the_geom, 4326)), ST_Y(ST_Transform(the_geom, 4326)) ), ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)) ) I think that says something like: Look through the II table For each row, transform the_geom to 4326, and take the X For each row, transform the_geom to 4326, and take the Y Make a point from that X,Y Check if that point is within a box I don't think that'll use an index, and I think it will do a bunch of transformation work for every row. I think that instead of transforming every row in II to 4326, you'd probably be better served by transforming your bounding box to 2163 one time. I think the SQL would look something like this: SELECT a, b FROM ll WHERE ST_Within( the_geom, ST_Transform( ST_MakeBox2D( ST_Point( -91.048, 45.956 ), ST_Point( -90.973, 46.007 ) ), 2163 ) ) In any case, you should probably try looking at the output of EXPLAIN or EXPLAIN ANALYZE to understand whether your index is being used, etc. Hi Michael, Yes indeed - I think you summarised this brilliantly :) If your geometries are stored as SRID 2163, then the index bounding boxes will also be stored in SRID 2163 - hence the index can only be used for queries involving other SRID 2163 bounding boxes. Otherwise PostgreSQL assumes it has to convert your entire geometry column to SRID 4326 first in order to calculate the intersection, which involves scanning the entire table and converting all the geometries on the fly... ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] insert to database - error
On 02/03/11 07:19, Robert Buckley wrote: Hi, Ubuntu 10.04 geoserver-2.0.2 postgresql 8.4 postgis I am experiencing the following: I can edit a postgis database geometry in Quantum GIS but I can´t "insert" into a postgis table inserted with shp3pgsql. ( I used the opengeo-suite windows version to upload the shp) my error is here message is here .. http://permalink.gmane.org/gmane.comp.gis.geoserver.user/26390 After days of scratching around the forums and reading this... http://lists.osgeo.org/pipermail/openlayers-users/2010-March/016801.html ..I decided to do the same a make a simple table in postgis - 1 x "id" column (type - integer, primary key, NotNull) and 1 x "the_geom" column ( type - geometry) I loaded it into geoserver and was able to start editing immediately Is it then something to do with the constraints inserted by shp2pgsql?..( I removed all of them one by one and tested the insert, but it still didn´t work) Possibly do to with the GID column? I ccan´t remove this column without breaking the geometry structure. Idon´t understand what the problem is with the table imported from a shapefile using shp2pgsql Hi Robert, The error message coming from Geoserver suggests that a prepared statement is failing on the database. Can you try editing your postgresql.conf to enable statement logging and restart PostgreSQL? Then next time Geoserver throws an error, you can look in the log file to see the exact SQL commands that were executed. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
El Tuesday 01 March 2011, Karl O. Pinc escribió: > Hi, > > I have a number of users each of which has their > own schema. I don't want the users to be able > to enable/disable the geospatialness of anyone > else's columns. What's the right way to > secure postgis so as to prevent this? It seems > that a single, global, geometry_columns is the > problem. > > I see a number of possibliities. > > If geometry_coulumns is all that needs to be secured > I could create the table in each user's schema. > If there's a lot of other infrastructure that needs > to be duplicated this would not work as well -- > the user's schemas would be all cluttered up. > But I can see where having multiple geometry_columns > tables could complicate an upgrade > > I could create a separate postgis schema for > each user, but that seems overkill and I'm not > at all clear on how $user is expanded in > the search_path and whether or not it'd be possible > to automatically have such schemas in the search > path. > > then again I could just forget about it and > hope the users don't kill each other. > > What's the best approach here? What if you write a trigger on every delete/update on the geometry_columns table? This trigger can cancel the delete/update if the user has no "permission" for that row. -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.es ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
>I have a number of users each of which has their >own schema. I don't want the users to be able >to enable/disable the geospatialness of anyone >else's columns. What's the right way to >secure postgis so as to prevent this? It seems >that a single, global, geometry_columns is the >problem. I guess a solution could be this: you set the geometry_column to read-only for all user except "postgres" user. After you can create a schema for every user must add table using only its schema (its username). After you create two security definer function with owner "postgres" that add and remove a row (a table) from geometry_column. and this two function has all the parameters needed for add a table to geometry_column. The function before add to geometry_columns retrieve the "session-user" and check if that is the same of the schema-parameter. If equal -> ok add if not-equel-> "error: you are not allowable to add a table to schema" Pay attention you must use "session-user" not "current-user" to check. regards, -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
