[postgis-users] is it possible to view postgis raster data using geoserver and openlayer?
i know it is given in your frequently asked questions that postgis raster data can be viewed with Mapserver and Qgis. is it possible to view postgis raster data using geoserver and openlayer? -- View this message in context: http://postgis.17.n6.nabble.com/is-it-possible-to-view-postgis-raster-data-using-geoserver-and-openlayer-tp4475310p4475310.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] does postgis create indexes for each tile or each band in a raster table?
-- View this message in context: http://postgis.17.n6.nabble.com/does-postgis-create-indexes-for-each-tile-or-each-band-in-a-raster-table-tp4475322p4475322.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] what is constraint enforce_out_db_rast?
UPDATE image2tiff set rast = ST_AddBand(ST_Band(rast, 2), rast ,1) where rid=5; ERROR: new row for relation image2tiff violates check constraint enforce_out_db_rast ** Error ** ERROR: new row for relation image2tiff violates check constraint enforce_out_db_rast SQL state: 23514 -- View this message in context: http://postgis.17.n6.nabble.com/what-is-constraint-enforce-out-db-rast-tp4475511p4475511.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] what is the query to build a multi band raster table from existing raster table of bands?
what is the query to build a multi band raster table from existing raster table of bands? -- View this message in context: http://postgis.17.n6.nabble.com/what-is-the-query-to-build-a-multi-band-raster-table-from-existing-raster-table-of-bands-tp4475646p4475646.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] qgis pgrouting plugin error
Dear All I am having a problem in installing the pgrouting plugin of QGis on Windows. I am new to this, thus need some step wise guide to figure it out.. Thnx Warm Regards Ali Kha-mis GIS Developer AA Associates Karachi. +92334-3584003___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] what is the query to build a multi band raster table from existing raster table of bands?
www.google.com www.postgis.org/documentation HTH On Thu, Feb 16, 2012 at 11:35 AM, Zelio Fernandes zelio.f...@gmail.comwrote: what is the query to build a multi band raster table from existing raster table of bands? -- View this message in context: http://postgis.17.n6.nabble.com/what-is-the-query-to-build-a-multi-band-raster-table-from-existing-raster-table-of-bands-tp4475646p4475646.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_AsJpeg
2012/2/16 Sandro Santilli s...@keybit.net: I don't think there's much to discuss. I'm sure a patch to psql would be welcome. Sorry, I did not realize that the solution is straight forward :- --Stefan ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How can i interchange(swap) the values of a band in postgis raster
Try like this: SELECT ST_Band(rast, ARRAY[2,1]) FROM image2tiff; -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 12:45 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] How can i interchange(swap) the values of a band in postgis raster SELECT ST_Band(rast,2,1) FROM image2tiff; ERROR: function st_band(raster, integer, integer) does not exist LINE 1: SELECT ST_Band(rast,2,1) FROM image2tiff; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. ** Error ** ERROR: function st_band(raster, integer, integer) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts. Character: 8 -- View this message in context: http://postgis.17.n6.nabble.com/How-can-i- interchange-swap-the-values-of-a-raster-bands-in-postgis-raster- tp4471437p4474862.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] is it possible to view postgis raster data using geoserver and openlayer?
Yes -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 4:43 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] is it possible to view postgis raster data using geoserver and openlayer? i know it is given in your frequently asked questions that postgis raster data can be viewed with Mapserver and Qgis. is it possible to view postgis raster data using geoserver and openlayer? -- View this message in context: http://postgis.17.n6.nabble.com/is-it-possible-to- view-postgis-raster-data-using-geoserver-and-openlayer- tp4475310p4475310.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] does postgis create indexes for each tile or each band in a raster table?
raster2pgsql optionally creates one index per table. You can create index yourself with the CREATE INDEX statement. Read the doc. -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 4:47 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] does postgis create indexes for each tile or each band in a raster table? -- View this message in context: http://postgis.17.n6.nabble.com/does-postgis- create-indexes-for-each-tile-or-each-band-in-a-raster-table- tp4475322p4475322.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] what is the query to build a multi band raster table from existing raster table of bands?
I feel like I'm responding to a question generator bot :-( Try something like: CREATE newtable AS SELECT ST_AddBand(ST_Band(table1.rast, 1), ST_Band(table2.rast, 1)) rast FROM table1, table2 WHERE table1.rid = table2.rid Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 6:35 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] what is the query to build a multi band raster table from existing raster table of bands? what is the query to build a multi band raster table from existing raster table of bands? -- View this message in context: http://postgis.17.n6.nabble.com/what-is-the- query-to-build-a-multi-band-raster-table-from-existing-raster-table-of-bands- tp4475646p4475646.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] what is constraint enforce_out_db_rast?
Search for enforce_out_db in: http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking03 Did you load your raster using the -R raster2plpgsql option? -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 5:45 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] what is constraint enforce_out_db_rast? UPDATE image2tiff set rast = ST_AddBand(ST_Band(rast, 2), rast ,1) where rid=5; ERROR: new row for relation image2tiff violates check constraint enforce_out_db_rast ** Error ** ERROR: new row for relation image2tiff violates check constraint enforce_out_db_rast SQL state: 23514 -- View this message in context: http://postgis.17.n6.nabble.com/what-is- constraint-enforce-out-db-rast-tp4475511p4475511.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] How to get all the attribute values of a classified image into postgis 2.0?
Where are those attributes stored? In the raster file itself? In a sister file? Which format? Could you send a sample? Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Thursday, February 16, 2012 5:21 AM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] How to get all the attribute values of a classified image into postgis 2.0? Can anybody help me the above query... -- View this message in context: http://postgis.17.n6.nabble.com/How-to-get-all- the-attribute-values-of-a-classified-image-into-postgis-2-0- tp4471830p4475435.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] setting up or accessing a host
Thanks Ben, I tried setting the pg_hba as you specified, and that seems to work. I'm only working with a development machine now, no sensitive data and can't be accessed outside the WAN, but these issues will become more important later. From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Thursday, February 16, 2012 2:14 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] setting up or accessing a host Yes - anytime you let anyone else connect to your machine it becomes a risk. You have to balance that with the usefulness of an isolated machine vs the value of being able to access it over a network. I guess in this case you are only allowing connections to the database, so that limits the damage that could happen. In this case, because you are letting anyone from that single ip address connect to any database, you may be pretty safe if you know the owner of that machine - and they are likely to be careful with their password. I'd be inclined towards : host specificdatabase individualusermachineIP/32 md5 and of course you could use hostssl if you were even more worried. Then it is a matter of what the individual user can access that becomes your concerns - for example, for most mapserver interfaces we have a 'mapuser' that can only select data from the necessary tables. I'm suprised that the fully qualified domain name of the machine is working - the documentation has : IP addresses can only be specified numerically, not as domain or host names cheers Ben On 15/02/2012, at 12:46 AM, Bistrais, Bob wrote: Hi Ben, I think I've had some success, thanks to your input. But please let me know if this is OK... I edited pg_hba.conf, adding a line like this: Host all all mymachinename md5 -where mymachinename is actually the fully qualified domain name of the machine. This works, in the sense that I can now display the data through other than localhost. But is this a good practice? Am I breaching security, or setting myself up for some other disaster? Thanks, Bob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Ben Madin Sent: Tuesday, February 14, 2012 10:42 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] setting up or accessing a host Bob, the security is by default tight - but it sounds like you want host based authentication (hba) You will need to read this page a few times : http://www.postgresql.org/docs/9.1/interactive/auth-pg-hba-conf.html and then before you do anything else edit your postgresql.conf file (about 60 lines down) to look like : listen_addresses = '*' # what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost', '*' = all # (change requires restart) Note the * in between the quotes (you may well have localhost there now). Then edit your pg_hba.conf file. note that you need to restart the server when you change the listen addresses, but you only need to reload it when you edit the pg_hba.conf file Also note that this can be really painful for users with dynamic ip address allocation. good luck. cheers Ben On 14/02/2012, at 11:04 PM, Bistrais, Bob wrote: So at this point I figured out how to get a PostGIS layer to display through MapServer, but that is only where host=localhost. Now I'm trying to figure out how to access the data from other machines. I'm looking through the documentation but haven't found out how yet. Can anyone point me in the right direction? ___ 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] Point to Polygon edge ST_DWithin
Hi, I need to create a WHERE statement which is true if a point is within 1 metres of the closest edge of a polygon (EPSG:27700). I have this working fine for point to point queries, but I am struggling with point to polygon. I also need to perform ST_Azimuth on the resulting data to return a bearing. Has anyone got any ideas? Thanks ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS Types Across Schemas
We are preparing our data for production, and have a few import procedures to do so. To keep the database organized, we are creating 2 schemas: public, and import. CREATE TABLE import.geo_shapes ( geo_shape_id serial NOT NULL, geocode character varying(9), geography import.geography(MultiPolygon,4326), geo_type integer, CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) ) When the import.geo_shapes table is finished being loaded, I ALTER the table's schema and change it to public so it becomes usable to the application. The problem I'm having is the geography column is still tied to the import namespace, even though the table's schema is public. To work around the problem I'm creating a new column on geo_shapes as public.geography and then using this UPDATE to get it to the correct type: ALTER TABLE geo_shapes RENAME COLUMN geography TO geography_temp; ALTER TABLE geo_shapes ADD COLUMN geography geography(MultiPolygon,4326); UPDATE geo_shapes SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) ALTER TABLE geo_shapes DROP COLUMN geography_temp; Is there a better way to do this? Seems like the ideal thing would be to have the types defined at the database level, but doesn't seem like that is possible. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] [Building PostGIS2.0] undefined reference to `libintl_printf'
Hi everyone, I got the following error while building (making) PostGIS 2.0: *** Making all in loader make[1]: Entering directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' /bin/sh ..//libtool --mode=link gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o ../liblwgeom/liblwgeom.la shp2pgsql-gui.res -o shp2pgsql-gui.exe -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -lintl-liconv -Lc:/projects/pg/pg91/lib -lpq -lintl gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o shp2pgsql-gui.res -o shp2pgsql-gui.exe ../liblwgeom/.libs/liblwgeom.a -L/c/projects/geos/rel-3.3.2/lib /c/projects/geos/rel-3.3.2/lib/libgeos_c.dll.a /c/projects/geos/rel-3.3.2/lib/libgeos.dll.a -L/c/projects/pg/pg91/lib /c/projects/pg/pg91/lib/libproj.a -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -Lc:/projects/pg/pg91/lib -lpq /mingw/lib/libintl.dll.a /c/mingw/lib/libiconv.dll.a -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib shp2pgsql-gui.o: In function `usage': c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3435: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3436: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3437: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3438: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3439: undefined reference to `libintl_printf' shp2pgsql-gui.o:c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3440: more undefined references to `libintl_printf' follow collect2: ld returned 1 exit status make[1]: *** [shp2pgsql-gui.exe] Error 1 make[1]: Leaving directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' make: *** [all] Error 1 *** Does anyone have any idea how to fix it? Thank you so much! - Edison Tam NGC SWE US -- View this message in context: http://postgis.17.n6.nabble.com/Building-PostGIS2-0-undefined-reference-to-libintl-printf-tp4477049p4477049.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] [Building PostGIS2.0] undefined reference to `libintl_printf'
I feel like in the link step the mingw is happier when you put static libraries at the end of the link line. Just a feeling though. P. On Thu, Feb 16, 2012 at 10:19 AM, GeoFox edison@ngc.com wrote: Hi everyone, I got the following error while building (making) PostGIS 2.0: *** Making all in loader make[1]: Entering directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' /bin/sh ..//libtool --mode=link gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o ../liblwgeom/liblwgeom.la shp2pgsql-gui.res -o shp2pgsql-gui.exe -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -lintl -liconv -Lc:/projects/pg/pg91/lib -lpq -lintl gcc -g -O2 -DDLL_EXPORT -DPIC -I/c/projects/pg/pg91/include -mwindows shpopen.o dbfopen.o shp2pgsql-core.o shpcommon.o shp2pgsql-gui.o getopt.o safileio.o pgsql2shp-core.o shp2pgsql-gui.res -o shp2pgsql-gui.exe ../liblwgeom/.libs/liblwgeom.a -L/c/projects/geos/rel-3.3.2/lib /c/projects/geos/rel-3.3.2/lib/libgeos_c.dll.a /c/projects/geos/rel-3.3.2/lib/libgeos.dll.a -L/c/projects/pg/pg91/lib /c/projects/pg/pg91/lib/libproj.a -Lc:/gtkbundle/lib -lgtk-win32-2.0 -lgdk-win32-2.0 -latk-1.0 -lgio-2.0 -lpangowin32-1.0 -lgdi32 -lpangocairo-1.0 -lgdk_pixbuf-2.0 -lpango-1.0 -lcairo -lgobject-2.0 -lgmodule-2.0 -lgthread-2.0 -lglib-2.0 -Lc:/projects/pg/pg91/lib -lpq /mingw/lib/libintl.dll.a /c/mingw/lib/libiconv.dll.a -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib -L/c/projects/geos/rel-3.3.2/lib -L/c/mingw/lib -L/mingw/lib shp2pgsql-gui.o: In function `usage': c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3435: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3436: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3437: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3438: undefined reference to `libintl_printf' c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3439: undefined reference to `libintl_printf' shp2pgsql-gui.o:c:\projects\postgis\postgis-2.0.0alpha5SVN\loader/shp2pgsql-gui.c:3440: more undefined references to `libintl_printf' follow collect2: ld returned 1 exit status make[1]: *** [shp2pgsql-gui.exe] Error 1 make[1]: Leaving directory `/c/projects/postgis/postgis-2.0.0alpha5SVN/loader' make: *** [all] Error 1 *** Does anyone have any idea how to fix it? Thank you so much! - Edison Tam NGC SWE US -- View this message in context: http://postgis.17.n6.nabble.com/Building-PostGIS2-0-undefined-reference-to-libintl-printf-tp4477049p4477049.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] distance between 2 polygons
Hi For each polygon I need to find the nearest polygon. I used ST_Distance(geometry, geometry) I succesfully did it but I want to make sure to interpret the result correctly Does A distance of 0 mean that the polygons touch each other or it means that their bounding box touch each other? What does a distance of 100 mean?. Is it the shortest distance between the 2 polygons or the distance between some random point on the polygon or on the surface? thanks Steve___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Types Across Schemas
You can run Probe_Geometry_Columns() after altering the schema of your imported table. Another option is to update the geometry columns table manually with something like: UPDATE geometry_columns SET f_schema_name = 'public' WHERE f_table_name = 'geo_shapes'; -Rob From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Steve Horn Sent: Thursday, February 16, 2012 12:03 PM To: postgis-users@postgis.refractions.net Subject: [postgis-users] PostGIS Types Across Schemas We are preparing our data for production, and have a few import procedures to do so. To keep the database organized, we are creating 2 schemas: public, and import. CREATE TABLE import.geo_shapes ( geo_shape_id serial NOT NULL, geocode character varying(9), geography import.geography(MultiPolygon,4326), geo_type integer, CONSTRAINT geo_shapes_geo_shape_id_pk PRIMARY KEY (geo_shape_id ) ) When the import.geo_shapes table is finished being loaded, I ALTER the table's schema and change it to public so it becomes usable to the application. The problem I'm having is the geography column is still tied to the import namespace, even though the table's schema is public. To work around the problem I'm creating a new column on geo_shapes as public.geography and then using this UPDATE to get it to the correct type: ALTER TABLE geo_shapes RENAME COLUMN geography TO geography_temp; ALTER TABLE geo_shapes ADD COLUMN geography geography(MultiPolygon,4326); UPDATE geo_shapes SET geography = public.ST_GeogFromWKB(import.ST_AsBinary(geography_temp)) ALTER TABLE geo_shapes DROP COLUMN geography_temp; Is there a better way to do this? Seems like the ideal thing would be to have the types defined at the database level, but doesn't seem like that is possible. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Mapserver/Postgis connection and WMS query
Hi List, (I did send a very similar question to the mapserver list two days ago but I didn't hear any comments or suggestions. This is a problem between mapserver and postgis). I'm using mapserver to display some rasters as WMS layers using Openlayers. At the moment I have all my rasters in a web-readable folder and it works fine. I want to try accessing the rasters using postgis, but I'm not sure exactly how to do this. I've read through this http://mapserver.org/input/vector/postgis.html and the relevant part of PostGIS in Action. My original map file (which worked) looked like this: LAYER NAME new_york_pop DATA/home//htdocs/data/new_york_pop.tif STATUS OFF TYPERASTER METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I tried changing this to the following: LAYER NAME new_york_pop TYPERASTER CONNECTIONTYPE POSTGIS CONNECTION host=18.00.00.00 dbname=raster_analysis user=postgres password=mypassword port=5434 DATA SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_800 AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name= 'new_york' STATUS OFF METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I want to clip my raster to the polygon and to display the clipped raster using a WMS query. The SQL query runs fine within the SQL console of Postgresql and returns a few tiffs, but it does not work on mapserver. My mapserver error log shows the following: [Tue Feb 14 12:16:29 2012].918681 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. [Tue Feb 14 12:16:29 2012].917535 msDrawMap(): rendering using outputformat named png (AGG/PNG). [Tue Feb 14 12:16:29 2012].918757 msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_raster AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york'' for layer 'new_york_pop'. [Tue Feb 14 12:16:29 2012].918766 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. I would appreciate any suggestions for debugging this. I have connected to the postgresql database using a php file from the same location so mapserver should have the correct permissions to connect (and my port is 5434, rather than the usual 5432). Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Mapserver/Postgis connection and WMS query
From what I know there is no way this should work. You have to read the raster layer as a GDAL layer. This is what is done in PostGIS in Action. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 16, 2012 3:14 PM To: PostGIS Users Discussion Subject: [postgis-users] Mapserver/Postgis connection and WMS query Hi List, (I did send a very similar question to the mapserver list two days ago but I didn't hear any comments or suggestions. This is a problem between mapserver and postgis). I'm using mapserver to display some rasters as WMS layers using Openlayers. At the moment I have all my rasters in a web-readable folder and it works fine. I want to try accessing the rasters using postgis, but I'm not sure exactly how to do this. I've read through this http://mapserver.org/input/vector/postgis.html and the relevant part of PostGIS in Action. My original map file (which worked) looked like this: LAYER NAME new_york_pop DATA/home//htdocs/data/new_york_pop.tif STATUS OFF TYPERASTER METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I tried changing this to the following: LAYER NAME new_york_pop TYPERASTER CONNECTIONTYPE POSTGIS CONNECTION host=18.00.00.00 dbname=raster_analysis user=postgres password=mypassword port=5434 DATA SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_800 AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york' STATUS OFF METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I want to clip my raster to the polygon and to display the clipped raster using a WMS query. The SQL query runs fine within the SQL console of Postgresql and returns a few tiffs, but it does not work on mapserver. My mapserver error log shows the following: [Tue Feb 14 12:16:29 2012].918681 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. [Tue Feb 14 12:16:29 2012].917535 msDrawMap(): rendering using outputformat named png (AGG/PNG). [Tue Feb 14 12:16:29 2012].918757 msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_raster AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york'' for layer 'new_york_pop'. [Tue Feb 14 12:16:29 2012].918766 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. I would appreciate any suggestions for debugging this. I have connected to the postgresql database using a php file from the same location so mapserver should have the correct permissions to connect (and my port is 5434, rather than the usual 5432). Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Mapserver/Postgis connection and WMS query
Pierre, What do you mean by reading the raster layer as a GDAL layer? In the PostGIS in Action raster example it seemed to just connect to the database, with no query using : Data PG:host=localhost . I tried removing CONNECTION and DATA and using the following: DATA PG:host=18.00.00.00 dbname=raster_analysis user=postgres port=5434 password=password schema='myschema' table='pop_800' This did not work either. Should I be just connecting to the data, and not trying to have a query there also? -David On Thu, Feb 16, 2012 at 3:31 PM, Pierre Racine pierre.rac...@sbf.ulaval.cawrote: From what I know there is no way this should work. You have to read the raster layer as a GDAL layer. This is what is done in PostGIS in Action. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto: postgis-users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 16, 2012 3:14 PM To: PostGIS Users Discussion Subject: [postgis-users] Mapserver/Postgis connection and WMS query Hi List, (I did send a very similar question to the mapserver list two days ago but I didn't hear any comments or suggestions. This is a problem between mapserver and postgis). I'm using mapserver to display some rasters as WMS layers using Openlayers. At the moment I have all my rasters in a web-readable folder and it works fine. I want to try accessing the rasters using postgis, but I'm not sure exactly how to do this. I've read through this http://mapserver.org/input/vector/postgis.html and the relevant part of PostGIS in Action. My original map file (which worked) looked like this: LAYER NAME new_york_pop DATA/home//htdocs/data/new_york_pop.tif STATUS OFF TYPERASTER METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I tried changing this to the following: LAYER NAME new_york_pop TYPERASTER CONNECTIONTYPE POSTGIS CONNECTION host=18.00.00.00 dbname=raster_analysis user=postgres password=mypassword port=5434 DATA SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_800 AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york' STATUS OFF METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I want to clip my raster to the polygon and to display the clipped raster using a WMS query. The SQL query runs fine within the SQL console of Postgresql and returns a few tiffs, but it does not work on mapserver. My mapserver error log shows the following: [Tue Feb 14 12:16:29 2012].918681 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. [Tue Feb 14 12:16:29 2012].917535 msDrawMap(): rendering using outputformat named png (AGG/PNG). [Tue Feb 14 12:16:29 2012].918757 msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_raster AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york'' for layer 'new_york_pop'. [Tue Feb 14 12:16:29 2012].918766 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. I would appreciate any suggestions for debugging this. I have connected to the postgresql database using a php file from the same location so mapserver should have the correct permissions to connect (and my port is 5434, rather than the usual 5432). Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Mapserver/Postgis connection and WMS query
This is a GDAL PostGIS connection string, not a direct PostGIS connection sting. Check: http://trac.osgeo.org/gdal/wiki/frmts_wtkraster.html Make sure gdal_translate is able to export your raster to a tiff before trying the same connection string with MapServer. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 16, 2012 3:55 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Mapserver/Postgis connection and WMS query Pierre, What do you mean by reading the raster layer as a GDAL layer? In the PostGIS in Action raster example it seemed to just connect to the database, with no query using : Data PG:host=localhost . I tried removing CONNECTION and DATA and using the following: DATA PG:host=18.00.00.00 dbname=raster_analysis user=postgres port=5434 password=password schema='myschema' table='pop_800' This did not work either. Should I be just connecting to the data, and not trying to have a query there also? -David On Thu, Feb 16, 2012 at 3:31 PM, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: From what I know there is no way this should work. You have to read the raster layer as a GDAL layer. This is what is done in PostGIS in Action. Pierre -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis- users- boun...@postgis.refractions.net] On Behalf Of David Quinn Sent: Thursday, February 16, 2012 3:14 PM To: PostGIS Users Discussion Subject: [postgis-users] Mapserver/Postgis connection and WMS query Hi List, (I did send a very similar question to the mapserver list two days ago but I didn't hear any comments or suggestions. This is a problem between mapserver and postgis). I'm using mapserver to display some rasters as WMS layers using Openlayers. At the moment I have all my rasters in a web-readable folder and it works fine. I want to try accessing the rasters using postgis, but I'm not sure exactly how to do this. I've read through this http://mapserver.org/input/vector/postgis.html and the relevant part of PostGIS in Action. My original map file (which worked) looked like this: LAYER NAME new_york_pop DATA/home//htdocs/data/new_york_pop.tif STATUS OFF TYPERASTER METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I tried changing this to the following: LAYER NAME new_york_pop TYPERASTER CONNECTIONTYPE POSTGIS CONNECTION host=18.00.00.00 dbname=raster_analysis user=postgres password=mypassword port=5434 DATA SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_800 AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york' STATUS OFF METADATA wms_title WMS new_york Raster wms_srsEPSG:900913 END END I want to clip my raster to the polygon and to display the clipped raster using a WMS query. The SQL query runs fine within the SQL console of Postgresql and returns a few tiffs, but it does not work on mapserver. My mapserver error log shows the following: [Tue Feb 14 12:16:29 2012].918681 tel:2012%5D.918681 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. [Tue Feb 14 12:16:29 2012].917535 tel:2012%5D.917535 msDrawMap(): rendering using outputformat named png (AGG/PNG). [Tue Feb 14 12:16:29 2012].918757 tel:2012%5D.918757 msDrawRasterLayerLow(): Unable to access file. Corrupt, empty or missing file 'SELECT ST_AsTIFF(ST_Clip(r.rast, 1, p.geom, NULL, TRUE)) FROM us_pop_raster AS r, cities AS p WHERE ST_Intersects(r.rast, p.geom) AND p.name = 'new_york'' for layer 'new_york_pop'. [Tue Feb 14 12:16:29 2012].918766 tel:2012%5D.918766 msDrawMap(): Image handling error. Failed to draw layer named 'new_york_pop'. I would appreciate any suggestions for debugging this. I have connected to the postgresql database using a php file from the same location so mapserver should have the correct permissions to connect (and my port is 5434, rather than the usual 5432). Thank you, David ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list
Re: [postgis-users] When to use Vacuum Analyze
Thanks, Stephen. Sheara Cohen Planner C A L T H O R P E A S S O C I A T E S 2095 ROSE STREET, SUITE 201, BERKELEY, CALIFORNIA, 94709 USA 510 809-1165 (direct) | 510-548-6800 x35 (main) | 510 548-6848 (fax) she...@calthorpe.commailto:she...@calthorpe.com | www.calthorpe.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Point to Polygon edge ST_DWithin
Hallo What you do is htat you use the linestrings defining the polygons instead of the polygon instead in the ST_Dwithin statement. Then you can also us ST_Closestpoint to find a line between the point and the closest point on the edge. This line can you use in ST_Azimuth. If your polygons have inner rings you can use ST_DumpRings to take also those in account. If you only want to use the exterior rings you can omit that part. But if there is multipolygons, you should use ST_Dump instead just to get single polygons. SELECT ST_Azimuth(point_table.the_geom, ST_ClosestPoint(edges.the_geom, point_table.the_geom)) as the_azimuth FROM (SELECT ST_ExteriorRing((ST_DumpRings(the_geom)).geom) as the_geom FROM polygons_table) edges , point_table WHERE ST_DWithin(edges.the_geom, point_table.the_geom, 1); HTH Nicklas On 02/16/2012 04:53 PM, uk52rob wrote: Hi, I need to create a WHERE statement which is true if a point is within 1 metres of the closest edge of a polygon (EPSG:27700). I have this working fine for point to point queries, but I am struggling with point to polygon. I also need to perform ST_Azimuth on the resulting data to return a bearing. Has anyone got any ideas? 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] distance between 2 polygons
Hallo Does A distance of 0 mean that the polygons touch each other or it means that their bounding box touch each other? From ST_Distance distance 0 menas that they are touching or overlapping each other What does a distance of 100 mean?. Is it the shortest distance between the 2 polygons or the distance between some random point on the polygon or on the surface? It is the shortest distance between two polygons. You can use ST_ShortestLine to find what that two points (first and last point in the resulting line) HTH Nicklas thanks Steve ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] ST_Crosses doesn't use index?
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_Crosses doesn't use index?
You might provide a description of the tables (\d at the psql prompt), and
perhaps the output of an EXPLAIN ANALYZE for this command. Any non-standard
config settings might be of relevance as well.
Greg Williamson
- Original Message -
From: Stefan Keller sfkel...@gmail.com
To: PostGIS Users Discussion postgis-users@postgis.refractions.net
Cc:
Sent: Thursday, February 16, 2012 3:51 PM
Subject: [postgis-users] ST_Crosses doesn't use index?
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
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_Crosses doesn't use index?
Stefan,
This has to do with your data / configuration, not ST_Crosses, as the
function clearly includes an index call.
CREATE OR REPLACE FUNCTION ST_Crosses(geometry,geometry)
RETURNS boolean
AS 'SELECT $1 $2 AND _ST_Crosses($1,$2)'
LANGUAGE 'SQL' IMMUTABLE;
P.
On Thu, Feb 16, 2012 at 3:51 PM, Stefan Keller sfkel...@gmail.com wrote:
Hi
ST_Crosses doc says that it uses the spatial index (if available).
But I'm afraid it's not. I'm using PostGIS 1.5 on Linux.
-- as it should be:
SELECT name
FROM osm_line a, osm_line b
WHERE a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes longer than 60 sec, 586 features.
-- now with ST_Contains:
SELECT name
FROM osm_line a, osm_line b
WHERE ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,a.way)
AND ST_Contains('BOX(8.775 47.245, 8.873 47.208'::box2d,b.way)
AND a.osm_id != b.osm_id
AND ST_Crosses(a.way, b.way)
-- takes 3.5sec, 586 features.
Obviously ST_Crosses does not consider the geometry index.
Why this?
Yours, Stefan
___
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_Crosses doesn't use index?
2012/2/17 Greg Williamson gwilliamso...@yahoo.com:
You might provide a description of the tables (\d at the psql prompt),
and perhaps the output of an EXPLAIN ANALYZE for this command.
Any non-standard config settings might be of relevance as well.
Greg Williamson
Ok; I realize that my first query was taking whole DB whereas the
second takes a spatial subset.
But what I still wonder is, why ST_Contains needs to be applied to the
second geometry (of table b) too since the index should really sort
out any outliers of table b
See below (I'm using PostGIS 1.5 on Linux).
Yours, Stefan
Table def.:
CREATE TABLE osm_line
(
osm_id integer,
...
... -- around 20 attributes from osm2pgsql import
...
tags hstore,
way geometry
) WITH (OIDS=FALSE);
Query 1:
gisdb= EXPLAIN ANALYZE
gisdb- SELECT a.name
gisdb- FROM osm_line a, osm_line b
gisdb- WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb- AND a.osm_id != b.osm_id
gisdb- AND ST_Crosses(a.way, b.way);
QUERY PLAN
--
Nested Loop (cost=60.59..14659.13 rows=713 width=14) (actual
time=157.097..26896.760 rows=954 loops=1)
Join Filter: ((a.osm_id b.osm_id) AND _st_crosses(a.way, b.way))
- Bitmap Heap Scan on osm_line a (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.398..16.426 rows=1702 loops=1)
Recheck Cond: ('01030...'::geometry way)
Filter: _st_contains('01030...'::geometry, way)
- Bitmap Index Scan on osm_line_index (cost=0.00..60.46
rows=1606 width=0) (actual time=1.255..1.255 rows=1849 loops=1)
Index Cond: ('01030...'::geometry way)
- Index Scan using osm_line_index on osm_line b
(cost=0.00..15.94 rows=3 width=1083) (actual time=0.036..0.420 rows=28
loops=1702)
Index Cond: (a.way b.way)
Total runtime: 26897.124 ms
(10 rows)
Query 2:
gisdb= EXPLAIN ANALYZE
gisdb- SELECT a.name
gisdb- FROM osm_line a, osm_line b
gisdb- WHERE ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),a.way)
gisdb- AND ST_Contains(ST_Transform(ST_SetSRID('BOX(8.775 47.245,
8.873 47.208)'::box2d, 4326),900913),b.way)
gisdb- AND a.osm_id != b.osm_id
gisdb- AND ST_Crosses(a.way, b.way);
QUERY PLAN
--
Nested Loop (cost=60.59..10353.28 rows=1 width=14) (actual
time=19.925..3088.990 rows=586 loops=1)
Join Filter: ((a.osm_id b.osm_id) AND _st_crosses(a.way, b.way))
- Bitmap Heap Scan on osm_line a (cost=60.59..5708.36 rows=535
width=1097) (actual time=1.432..12.943 rows=1702 loops=1)
Recheck Cond: ('01030...'::geometry way)
Filter: _st_contains('01030...'::geometry, way)
- Bitmap Index Scan on osm_line_index (cost=0.00..60.46
rows=1606 width=0) (actual time=1.285..1.285 rows=1849 loops=1)
Index Cond: ('01030...'::geometry way)
- Index Scan using osm_line_index on osm_line b (cost=0.00..8.42
rows=1 width=1083) (actual time=0.464..1.069 rows=12 loops=1702)
Index Cond: (('01030...'::geometry b.way)
AND (a.way b.way))
Filter: _st_contains('01030...'::geometry, b.way)
Total runtime: 3089.228 ms
(11 rows)
gisdb=
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
