Re: [postgis-users] Can I add geometry column without use AddGeometryColumn?
On Fri, Jul 16, 2010 at 10:28:50AM +0700, nguyen thuy ai vy wrote: Hello everybody, Is there any problem if I add a geometry by this way? Firstly, I created table has a column with geometry type. Then, I added check constraints for this geometry column like this: CONSTRAINT con_poi_point_enforce_dims_chk CHECK (ndims(poi_point) = 2), CONSTRAINT con_poi_point_enforce_geotype_chk CHECK (geometrytype(poi_point) = ''POINT''::text OR poi_point IS NULL), CONSTRAINT con_poi_point_enforce_srid_chk CHECK (srid(poi_point) = 4326) *some* clients need an entry in geometry_columns to use your spatial data. If your client doesn't, there's no problem. As for the constraint, some management stored procedures use their name and definition to figure out the record to put in geometry_columns for you. But other don't. So, short story: you are completely fine within postgis to add a column manually (at CREATE TABLE or ALTER TABLE or SELECT INTO time). --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] MySQL - Postgresql - habitat column name
On 15 July 2010 20:37, Michael A. Peters mpet...@shastaherps.org wrote: I'm currently working on a migration of my web site to postgresql so that I can use PostGIS. I'm still working on the devel side but basically - mysqldump database dump.sql perl mysql2pgsql.perl dump.sql load.sql psql database load.sql does the trick, now it's cleaning up my sql in my code. I used MDB2 so once I finally figured out how to make a web user with necessary permissions most of it just worked but - I had a table with a column named habitat. On a query on that table, query failed. When manually applying the query, it said habitat did not exist. I looked in the load file (there's no describe table ??) and habitat was changed to habyteaat but I can find nothing about habitat being a special word to postgresql. Is that a bug in the perl script I can just sed to fix the output or do I really need to use a different column name? Hmm funny: seems that the perl script is replacing every instance of 'bit' to 'bytea' (a PG datatype). definitely, 'habitat' is a valid column name. Nicolas ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Info on topology
Hi, I like to test the topology suite for Postgis. There is some document to help to install and use topology ? Thx, -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Info on topology
On Fri, Jul 16, 2010 at 04:00:22PM +0200, Andrea Peri wrote: There is some document to help to install and use topology ? See topology/README and the wiki: http://trac.osgeo.org/postgis/wiki/UsersWikiPostgisTopology --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] shp2pgsql
Hi I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. Can anyone suggest where a clean copy can be found and instructions on how to install it?? Bob___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql
Bob Pawley wrote: Hi I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. Can anyone suggest where a clean copy can be found and instructions on how to install it?? Bob Hi Bob, It's included with the PostGIS windows installer, and can normally be found in the PostgreSQL bin directory once the installer has been run. 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] shp2pgsql
Hi Bob, On 16 July 2010 16:46, Bob Pawley rjpaw...@shaw.ca wrote: I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. If you've got PostgreSQL installed then you should already have a copy of the library. Find the 'bin' directory within your PostgreSQL installation e.g. C:\PostgreSQL\8.4\bin and copy libiconv-2.dll to libiconv2.dll. Also, make sure that your PATH variable includes that 'bin' directory (check with 'echo %PATH%' from a DOS prompt). HTH, Will ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Execution plan with spatial index
Greetings, I have a table with around a million rows with each row containing a * geography* POINT (I'm covering the entire world). With these rows I need to perform many nearest neighbor searches to locate other entities within a range of 0 to 100km. The location field has a GiST index With 5,000 records in the table my average query took around 0.5ms; however, now I have a million records the query time has gone up to around 4ms. I did a reindex/vacuum etc. My goal is to try and make this query as fast as possible since it underpins everything I'm doing and the target hardware won't be as powerful as my development box. As well as the location column, I also have a entity_type column that is a simple integer - 1, 2, ... (n)... I figured I could improve performance by adding an index on the entity_type column, then filter the rowset prior to nearest neighbor search. My logic was that it must be quicker to isolate a 10% subset of the records using a simple integer index before feeding in to the expensive GiST index. Unfortunately when I did this, PostgresSQL didn't use my entity_type_idx at all. Instead, it did the nearest neighbor search using the GiST index, then did a simple filter on the collected records based on the entity_type. I tried a few tricks to make it use the index but no-luck. Any ideas for speeding this up would be very much appreciated! Right now my best idea would be to have separate tables for each entity type, but that wouldn't be fun as I don't know the entity types in advance. cheers, -chris Here is pseudo-code of the query and execution plan/analysis. CB_GetPlace() is one of my helper functions that returns a geography from an entity id (marked *stable*). *SELECT* entity_id, category_id, ST_Distance(location, CB_GetPlace(someEntityID)) as arcLength *FROM* entities *WHERE* category_id = 1 *AND* ST_DWithin(location, CB_GetPlace(someEntityID), someRadius) *ORDER BY*arcLength; Sort (cost=26.64..26.64 rows=1 width=140) (actual time=4.207..4.209 rows=16 loops=1) Sort Key: (_st_distance(location, cb_getplace(someEntityID::bigint), 0::double precision, true)) Sort Method: quicksort Memory: 18kB - Index Scan using place_idx on entities (cost=0.03..26.63 rows=1 width=140) (actual time=1.691..4.187 rows=16 loops=1) Index Cond: (location _st_expand(cb_getplace(someEntityID::bigint), someRadius::double precision)) Filter: ((urt_id = 1) AND (cb_getplace(someEntityID::bigint) _st_expand(location, someRadius::double precision)) AND _st_dwithin(location, cb_getplace(someEntityID::bigint), someRadius::double precision, true)) Total runtime: 4.242 ms If it matters, my test platform is PostGIS 1.5.1 with PostgreSQL 8.4.4-1 (Windows 32bit build) though my target platform is Ubuntu x64. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql
I did find in the bin file but it is named libiconv-2 and the error was looking for libiconv2. Is there a way around this?? Bob -- From: Mark Cave-Ayland mark.cave-ayl...@siriusit.co.uk Sent: Friday, July 16, 2010 8:57 AM To: PostGIS Users Discussion postgis-users@postgis.refractions.net Subject: Re: [postgis-users] shp2pgsql Bob Pawley wrote: Hi I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. Can anyone suggest where a clean copy can be found and instructions on how to install it?? Bob Hi Bob, It's included with the PostGIS windows installer, and can normally be found in the PostgreSQL bin directory once the installer has been run. 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql
I'm not sure what you mean by - copy libiconv-2.dll to libiconv2.dll. Do you mean rename?? Bob -- From: William Furnass w...@thearete.co.uk Sent: Friday, July 16, 2010 9:00 AM To: PostGIS Users Discussion postgis-users@postgis.refractions.net Subject: Re: [postgis-users] shp2pgsql Hi Bob, On 16 July 2010 16:46, Bob Pawley rjpaw...@shaw.ca wrote: I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. If you've got PostgreSQL installed then you should already have a copy of the library. Find the 'bin' directory within your PostgreSQL installation e.g. C:\PostgreSQL\8.4\bin and copy libiconv-2.dll to libiconv2.dll. Also, make sure that your PATH variable includes that 'bin' directory (check with 'echo %PATH%' from a DOS prompt). HTH, Will ___ 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] 'Clustering' records in space and time
Thanks Pierre and Dylan for your helpful replies. FYI my dataset is 90K records describing events that occurred over 14 years over an area of 50^2m. The suggestion of using R's PAM and CLARA functions for clustering lead me to the 'dbscan' algorithm which may well be a better choice for my needs as one doesn't need to know in advance how many clusters require identification. Clusters require a minimum no of points (MinPts) within a maximum distance (eps) around one of its members (the seed). Any point within eps around any point which satisfies the seed condition is a cluster member (recursively). Some points may not belong to any clusters. (http://bm2.genes.nig.ac.jp/RGM2/R_current/library/fpc/man/dbscan.html). Another approach I'm considering is to discretize 2D space and time (three dimensions) into a cellular matrix, associate each event with a cell and amalgamate all records that have the same cell reference. This would of course fail to cluster 'close' events that happen to fall either side of a cell divide but _might_ be easy to implement using say PL/SQL. For reference it appears that a clustering function for PostGIS has already been proposed: http://opengeo.org/products/coredevelopment/postgis/bi-utilities/ Thanks again for pointing me towards PAM/CLARA. Cheers, Will On 15 July 2010 21:33, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: I would suggest you ask your question to the r-sig-geo mailing list. You will get a R solution. You can then get your PostGIS table from R using the gdal/ogr package or use PL/R in PostgreSQL. Pierre -Original Message- From: Dylan Beaudette [mailto:debeaude...@ucdavis.edu] Sent: 15 juillet 2010 16:02 To: Pierre Racine Cc: PostGIS Users Discussion; w...@thearete.co.uk Subject: Re: [postgis-users] 'Clustering' records in space and time On Thursday 15 July 2010, Pierre Racine wrote: What should happen when event A is at a distance n minus epsilon from B, B is at a distance n-epsilon from C but A is at a distance 2*n-epsilon from C? Should A and C be in the same cluster with B? Pierre Interesting. The choice of clustering algorithm would need to be based on the questions the OP was trying to answer. Without much thought (warning!) I pictured a 3D space (x, y, time) partitioned around medoids (PAM algorithm) of data. In this very simple case chunks of data in (x, y, time) space would be collected based on their proximity. For this to work, space and time coordinates would need to be standardized accordingly... For x and y, I think that subtracting the mean and dividing by the standard deviation should do. I am not sure about the standardization of time... maybe the same thing, but applied to the number of seconds | minutes | hours | days elapsed since the start of the experiment? Dylan -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Dylan Beaudette Sent: 15 juillet 2010 15:10 To: w...@thearete.co.uk; PostGIS Users Discussion Subject: Re: [postgis-users] 'Clustering' records in space and time Hi, Can you give us some hints about your data? 1. how many records 2. temporal domain (i.e. 1 year?) 3. spatial domain (local, regional, continental?) If you don't have too much data, you may be able to standardize them, and apply an algorithm like PAM, or CLARA (see cluster package in R). Cheers, Dylan On Thursday 15 July 2010, William Furnass wrote: I have a PostGIS table of records describing events therefore the table has a timestamp attribute. I wish to replace 'clusters' of events that occur within a m-hour window and a spatial radius of n with single events which have the mean timestamp and central position of the cluster. I understand that I can quantize my data spatially using the St_SnapToGrid function but using this function alone I lose some of the distinct events that occurred at the same point in space but at very different times (it's my understanding that St_SnapToGrid only allows one point to be stored at each node in the grid). Also, I am unsure as to how I could use St_SnapToGrid in such a way so as not to relocate points that are unique within the aforementioned spatial and temporal window boundaries. Has anyone any suggestions as to how this can be achieved programmatically using SQL (rather than a graphical tool)? Should I perhaps be looking to use R to spatially and temporally cluster my data? Apologies if the description of my problem isn't particularly clear; it's been a long day:) ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Dylan Beaudette Soil Resource Laboratory http://casoilresource.lawr.ucdavis.edu/ University of California at Davis 530.754.7341 ___
Re: [postgis-users] 'Clustering' records in space and time
I don't really like those grid or rounding methods pretending to cluster things. You can totally break all your natural clusters with any of those methods. We could call them aggregating methods but please not clustering... Just throwing a stone in the water. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of William Furnass Sent: 16 juillet 2010 13:00 To: PostGIS Users Discussion Subject: Re: [postgis-users] 'Clustering' records in space and time Thanks Pierre and Dylan for your helpful replies. FYI my dataset is 90K records describing events that occurred over 14 years over an area of 50^2m. The suggestion of using R's PAM and CLARA functions for clustering lead me to the 'dbscan' algorithm which may well be a better choice for my needs as one doesn't need to know in advance how many clusters require identification. Clusters require a minimum no of points (MinPts) within a maximum distance (eps) around one of its members (the seed). Any point within eps around any point which satisfies the seed condition is a cluster member (recursively). Some points may not belong to any clusters. (http://bm2.genes.nig.ac.jp/RGM2/R_current/library/fpc/man/dbscan.html). Another approach I'm considering is to discretize 2D space and time (three dimensions) into a cellular matrix, associate each event with a cell and amalgamate all records that have the same cell reference. This would of course fail to cluster 'close' events that happen to fall either side of a cell divide but _might_ be easy to implement using say PL/SQL. For reference it appears that a clustering function for PostGIS has already been proposed: http://opengeo.org/products/coredevelopment/postgis/bi-utilities/ Thanks again for pointing me towards PAM/CLARA. Cheers, Will On 15 July 2010 21:33, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: I would suggest you ask your question to the r-sig-geo mailing list. You will get a R solution. You can then get your PostGIS table from R using the gdal/ogr package or use PL/R in PostgreSQL. Pierre -Original Message- From: Dylan Beaudette [mailto:debeaude...@ucdavis.edu] Sent: 15 juillet 2010 16:02 To: Pierre Racine Cc: PostGIS Users Discussion; w...@thearete.co.uk Subject: Re: [postgis-users] 'Clustering' records in space and time On Thursday 15 July 2010, Pierre Racine wrote: What should happen when event A is at a distance n minus epsilon from B, B is at a distance n-epsilon from C but A is at a distance 2*n-epsilon from C? Should A and C be in the same cluster with B? Pierre Interesting. The choice of clustering algorithm would need to be based on the questions the OP was trying to answer. Without much thought (warning!) I pictured a 3D space (x, y, time) partitioned around medoids (PAM algorithm) of data. In this very simple case chunks of data in (x, y, time) space would be collected based on their proximity. For this to work, space and time coordinates would need to be standardized accordingly... For x and y, I think that subtracting the mean and dividing by the standard deviation should do. I am not sure about the standardization of time... maybe the same thing, but applied to the number of seconds | minutes | hours | days elapsed since the start of the experiment? Dylan -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Dylan Beaudette Sent: 15 juillet 2010 15:10 To: w...@thearete.co.uk; PostGIS Users Discussion Subject: Re: [postgis-users] 'Clustering' records in space and time Hi, Can you give us some hints about your data? 1. how many records 2. temporal domain (i.e. 1 year?) 3. spatial domain (local, regional, continental?) If you don't have too much data, you may be able to standardize them, and apply an algorithm like PAM, or CLARA (see cluster package in R). Cheers, Dylan On Thursday 15 July 2010, William Furnass wrote: I have a PostGIS table of records describing events therefore the table has a timestamp attribute. I wish to replace 'clusters' of events that occur within a m-hour window and a spatial radius of n with single events which have the mean timestamp and central position of the cluster. I understand that I can quantize my data spatially using the St_SnapToGrid function but using this function alone I lose some of the distinct events that occurred at the same point in space but at very different times (it's my understanding that St_SnapToGrid only allows one point to be stored at each node in the grid). Also, I am unsure as to how I could use St_SnapToGrid in such a way so as not to relocate points that are unique within the aforementioned spatial and temporal window boundaries. Has anyone any suggestions as to how this can be achieved programmatically using SQL (rather than a graphical
Re: [postgis-users] 'Clustering' records in space and time
Hi Will, Yes, DBSCAN is a much better choice for what you want to do. However, how to include the temporal element becomes something of an issue with DBSCAN or PAM/CLARA. Then there are the issues of selecting an epsilon radius and min pts for the DBSCAN algorithm. At this point, given the nature your questions, you are likely to find the R-sig-Geo mailing list a better choice than the PostGIS-User list. Here is the link to subscribe to that list: https://stat.ethz.ch/mailman/listinfo/r-sig-geo Dan On 07/16/2010 09:59 AM, William Furnass wrote: Thanks Pierre and Dylan for your helpful replies. FYI my dataset is 90K records describing events that occurred over 14 years over an area of 50^2m. The suggestion of using R's PAM and CLARA functions for clustering lead me to the 'dbscan' algorithm which may well be a better choice for my needs as one doesn't need to know in advance how many clusters require identification. Clusters require a minimum no of points (MinPts) within a maximum distance (eps) around one of its members (the seed). Any point within eps around any point which satisfies the seed condition is a cluster member (recursively). Some points may not belong to any clusters. (http://bm2.genes.nig.ac.jp/RGM2/R_current/library/fpc/man/dbscan.html). Another approach I'm considering is to discretize 2D space and time (three dimensions) into a cellular matrix, associate each event with a cell and amalgamate all records that have the same cell reference. This would of course fail to cluster 'close' events that happen to fall either side of a cell divide but _might_ be easy to implement using say PL/SQL. For reference it appears that a clustering function for PostGIS has already been proposed: http://opengeo.org/products/coredevelopment/postgis/bi-utilities/ Thanks again for pointing me towards PAM/CLARA. Cheers, Will On 15 July 2010 21:33, Pierre Racinepierre.rac...@sbf.ulaval.ca wrote: I would suggest you ask your question to the r-sig-geo mailing list. You will get a R solution. You can then get your PostGIS table from R using the gdal/ogr package or use PL/R in PostgreSQL. Pierre -Original Message- From: Dylan Beaudette [mailto:debeaude...@ucdavis.edu] Sent: 15 juillet 2010 16:02 To: Pierre Racine Cc: PostGIS Users Discussion; w...@thearete.co.uk Subject: Re: [postgis-users] 'Clustering' records in space and time On Thursday 15 July 2010, Pierre Racine wrote: What should happen when event A is at a distance n minus epsilon from B, B is at a distance n-epsilon from C but A is at a distance 2*n-epsilon from C? Should A and C be in the same cluster with B? Pierre Interesting. The choice of clustering algorithm would need to be based on the questions the OP was trying to answer. Without much thought (warning!) I pictured a 3D space (x, y, time) partitioned around medoids (PAM algorithm) of data. In this very simple case chunks of data in (x, y, time) space would be collected based on their proximity. For this to work, space and time coordinates would need to be standardized accordingly... For x and y, I think that subtracting the mean and dividing by the standard deviation should do. I am not sure about the standardization of time... maybe the same thing, but applied to the number of seconds | minutes | hours | days elapsed since the start of the experiment? Dylan -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Dylan Beaudette Sent: 15 juillet 2010 15:10 To: w...@thearete.co.uk; PostGIS Users Discussion Subject: Re: [postgis-users] 'Clustering' records in space and time Hi, Can you give us some hints about your data? 1. how many records 2. temporal domain (i.e. 1 year?) 3. spatial domain (local, regional, continental?) If you don't have too much data, you may be able to standardize them, and apply an algorithm like PAM, or CLARA (see cluster package in R). Cheers, Dylan On Thursday 15 July 2010, William Furnass wrote: I have a PostGIS table of records describing events therefore the table has a timestamp attribute. I wish to replace 'clusters' of events that occur within a m-hour window and a spatial radius of n with single events which have the mean timestamp and central position of the cluster. I understand that I can quantize my data spatially using the St_SnapToGrid function but using this function alone I lose some of the distinct events that occurred at the same point in space but at very different times (it's my understanding that St_SnapToGrid only allows one point to be stored at each node in the grid). Also, I am unsure as to how I could use St_SnapToGrid in such a way so as not to relocate points that are unique within the aforementioned spatial and temporal window boundaries. Has anyone any suggestions as to how this can be achieved programmatically using SQL (rather than a graphical tool)?
[postgis-users] intersect operation in 3d?
Hi PostGIS folks, I'm trying to find a way to read two 3-dimensional polygons and test if they intersect. It's for a radar-bird-tracking application. I'm hoping that PostGIS is a solution. I've been scanning the Mailing list archives, and I gather that at least some of the spatial operations which PostGIS provides are 3d-enabled. Which surprises me, as my understanding was that PostGIS spatial operations descend from the Java Topological Suite via GEOS, and JTS advertises itself as being specifically for 2-d linear modelling. Could I count on the ST_Intersects function to work with 3d data? Would there likely be any special considerations for 3d? http://postgis.refractions.net/documentation/manual-1.5/ST_Intersects.html cheers, Hugh -- Hugh Stimson / 778.373.1024 / hughstimson.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Error loading lwpostgis.sql
I'm using CentOS 5.4 up to patch level. Pretty much stock CentOS + EPEL. Only have updated php which shouldn't effect postgresql and a gnome library that had a bug (and there I patched the src.rpm, same version as stock). I usually don't like to replace vendor provided stuff. postgresql-8.1.11-1.el5_1.1 postgis-1.3.2-1.el5 It's a 32-bit install if it matters. As the postgres user I run the following command: psql -d shastaherps -f /usr/share/pgsql/contrib/lwpostgis.sql 2 error.log In error.log I find the following: psql:/usr/share/pgsql/contrib/lwpostgis.sql:2356: ERROR: language plpgsql does not exist That is then followed by a bunch of current transaction is aborted messages. Is there a step I missed? - Michael A. Peters http://www.shastaherps.org/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] [solved] Re: Error loading lwpostgis.sql
In error.log I find the following: psql:/usr/share/pgsql/contrib/lwpostgis.sql:2356: ERROR: language plpgsql does not exist doh - sorry, right there in the doc above the command I ran - # createlang plpgsql [yourdatabase] I feel dumb :-/ - Michael A. Peters http://www.shastaherps.org/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] shp2pgsql
Bob, Copy is better. So copy the file as William said, and rename the new copy libiconv2.dll. Some programs refer to it by libiconv-2.dll and some as libiconv2.dll. It's a complicated story, but they are generally interchangeable. However if you simply rename the existing, you'll break some other application. Its sounds like you are running an older shp2pgsql though because I think in later versions we changed to libiconv-2.dll to be consistent with PostgreSQL windows build Can you when you have your system working, at dos prompt do a shp2pgsql And let us know the version number it returns at top. It's the part that reads like RCSID: $Id: shp2pgsql-core.h 5098 2010-01-04 05:47:04Z pramsey $ RELEASE: 1.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1 Thanks, Leo and Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Bob Pawley Sent: Friday, July 16, 2010 12:46 PM To: w...@thearete.co.uk; PostGIS Users Discussion Subject: Re: [postgis-users] shp2pgsql I'm not sure what you mean by - copy libiconv-2.dll to libiconv2.dll. Do you mean rename?? Bob -- From: William Furnass w...@thearete.co.uk Sent: Friday, July 16, 2010 9:00 AM To: PostGIS Users Discussion postgis-users@postgis.refractions.net Subject: Re: [postgis-users] shp2pgsql Hi Bob, On 16 July 2010 16:46, Bob Pawley rjpaw...@shaw.ca wrote: I just attempted to use shp2pgsql and received an error message about libiconv2.dll not being installed on my computer. If you've got PostgreSQL installed then you should already have a copy of the library. Find the 'bin' directory within your PostgreSQL installation e.g. C:\PostgreSQL\8.4\bin and copy libiconv-2.dll to libiconv2.dll. Also, make sure that your PATH variable includes that 'bin' directory (check with 'echo %PATH%' from a DOS prompt). HTH, Will ___ 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] Execution plan with spatial index
Chris, Did you confirm that your btree index works fine without the spatial query part? Run the query without the spatial part at all. If you don't have enough data distinct categories, its often faster for PostgreSQL not to use the btree index or at least it thinks so. Its also sometimes some problem with differences in data types used that it can't use an index. I don't think that is your issue here though. One way to try to force it to use all indexes is to do a set enable_seqscan = off; Before running the query and if that works, increasing the seq_page_cost Hope that helps Leo and Regina, h http://www.postgis.us ttp://www.postgis.us _ From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Christian Beaumont Sent: Friday, July 16, 2010 12:31 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] Execution plan with spatial index Greetings, I have a table with around a million rows with each row containing a geography POINT (I'm covering the entire world). With these rows I need to perform many nearest neighbor searches to locate other entities within a range of 0 to 100km. The location field has a GiST index With 5,000 records in the table my average query took around 0.5ms; however, now I have a million records the query time has gone up to around 4ms. I did a reindex/vacuum etc. My goal is to try and make this query as fast as possible since it underpins everything I'm doing and the target hardware won't be as powerful as my development box. As well as the location column, I also have a entity_type column that is a simple integer - 1, 2, ... (n)... I figured I could improve performance by adding an index on the entity_type column, then filter the rowset prior to nearest neighbor search. My logic was that it must be quicker to isolate a 10% subset of the records using a simple integer index before feeding in to the expensive GiST index. Unfortunately when I did this, PostgresSQL didn't use my entity_type_idx at all. Instead, it did the nearest neighbor search using the GiST index, then did a simple filter on the collected records based on the entity_type. I tried a few tricks to make it use the index but no-luck. Any ideas for speeding this up would be very much appreciated! Right now my best idea would be to have separate tables for each entity type, but that wouldn't be fun as I don't know the entity types in advance. cheers, -chris Here is pseudo-code of the query and execution plan/analysis. CB_GetPlace() is one of my helper functions that returns a geography from an entity id (marked stable). SELECT entity_id, category_id, ST_Distance(location, CB_GetPlace(someEntityID)) as arcLength FROM entities WHERE category_id = 1 AND ST_DWithin(location, CB_GetPlace(someEntityID), someRadius) ORDER BY arcLength; Sort (cost=26.64..26.64 rows=1 width=140) (actual time=4.207..4.209 rows=16 loops=1) Sort Key: (_st_distance(location, cb_getplace(someEntityID::bigint), 0::double precision, true)) Sort Method: quicksort Memory: 18kB - Index Scan using place_idx on entities (cost=0.03..26.63 rows=1 width=140) (actual time=1.691..4.187 rows=16 loops=1) Index Cond: (location _st_expand(cb_getplace(someEntityID::bigint), someRadius::double precision)) Filter: ((urt_id = 1) AND (cb_getplace(someEntityID::bigint) _st_expand(location, someRadius::double precision)) AND _st_dwithin(location, cb_getplace(someEntityID::bigint), someRadius::double precision, true)) Total runtime: 4.242 ms If it matters, my test platform is PostGIS 1.5.1 with PostgreSQL 8.4.4-1 (Windows 32bit build) though my target platform is Ubuntu x64. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
