[postgis-users] How can i interchange(swap) the values of a band in postgis raster
How can i interchange(swap) the values of a band in postgis raster. for example if there are 4 raster bands in the raster table and suppose if if i want to interchange the value of band1 with band2, how do i do it i did not find any such specific function in postgis 2.0 ...can it be done using ST_SetValue??? -- View this message in context: http://postgis.17.n6.nabble.com/How-can-i-interchange-swap-the-values-of-a-band-in-postgis-raster-tp4471437p4471437.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] PostGIS on an old NAS drive or a new rendering machine
I am being offered a choice at work to move my PostGIS to a new server. I am being offered two choices. I do not do particularly large queries on QGIS, and the database are mainly used within the business. Some of my tables are quite large (9,000,000 rows of polygon data) but there isn't too much in the way of complex joins or complex spatial queries. The machine will also have apache and probably GeoServer as well. 1) Use an old NAS drive (about 4 or 5 years old). Windows Storage Server 2003 R2 Intel Xeon CPU 5150@2.66GHz 1GB RAM 1TB HD This machine would only be used for PostGIS and storing Raster files for access on the file server. 2) Run PostGIS on a brand new rendering machine Intel 3960x Extreme edition 6 core processor LGA2011 motherboard 16gb Quad Channel ram ATI FirePro V5900 2Gb 1.5TB 6Gb/s HD DVD/CD Windows 7 64bit This machine will be also used to render 3D Max work, so I would be sharing this machine with others. Can anyone comment on which of these would be better for me to use? I am concerned that whilst rendering is taking place on the render machine it will slow PostGIS down a lot. Is this the case? Regards, James James Stott BSc (Hons) MSc | Senior Professional Nicholas Pearson Associates | 30 Brock Street | Bath | BA1 2LN T: 01225 445548 | M: - http://www.npaconsult.co.uk/ http://www.npaconsult.co.uk/http://www.npaconsult.co.uk/about-news.asp http://www.npaconsult.co.uk/terms.asp This Electronic Transmission is intended only for the attention of the addressee. It may contain privileged and confidential information. If you are not the person for whom it is intended, you must not copy, distribute or take any action in reliance on it. If you have received this electronic transmission in error please notify us immediately by telephone, delete the transmission and destroy any hard copies. Nicholas Pearson Associates has used all reasonable efforts to ensure that this message and any attachments are free from viruses. Nicholas Pearson Associates, 30 Brock Street, Bath, BA1 2LN , Tel: +44 (0) 01225 445548, Fax: +44 (0) 01225 312387, Website: http://www.npaconsult.co.uk/ ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to get all the attribute values of a classified image into postgis 2.0?
How to get all the attribute values of a classified image into postgis 2.0? it only shows me the columns of raster table as Rid,Rast,file whereas the columns O_id,value,count,Red,Green,Blue,opacity,classname are not shown as columns? PLEASE NOTE THAT FOR THIS IMAGE IT SHOWS me all this columns(O_id,value,count,Red,Green,Blue,opacity,classname) IN ARCGIS but when i import this same image in postgis2.0 table ..this table has none of those columns. -- 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-tp4471830p4471830.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] HOW to do CLASSIFICATION OF A IMAGE in POSTGIS 2.0???
HOW to do CLASSIFICATION OF A IMAGE in POSTGIS 2.0??? -- View this message in context: http://postgis.17.n6.nabble.com/HOW-to-do-CLASSIFICATION-OF-A-IMAGE-in-POSTGIS-2-0-tp4471836p4471836.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] PostGIS on an old NAS drive or a new rendering machine
I don't have hard numbers, but I was able to get a factor of 100x in my queries by a combination of a processor upgrade, additional DRAM, and shifting to SSDs. I attribute about 10-15x to the processor and another 3-5x from the SSD with about 2x from the DRAM, but as I upgraded everything at once, I can't provide any solid breakdown. My task is different with more complex joins but the dataset is equivalent in size. If you go with the first option, find out if upgrading the DRAM is an option. Even 2 GB would be a significant improvement. Get an SSD if there is any budget for such; the improvements in random access reads are very useful for database applications. If you go with the second option, rendering is typically a background activity. Find out if PostGIS/PostgreSQL/Middleware can be run at a higher priority. Even then you may see some latency in practice as the associated memory needs to be run from swap. On Feb 15, 2012, at 4:57 AM, James Stott wrote: I am being offered a choice at work to move my PostGIS to a new server. I am being offered two choices. I do not do particularly large queries on QGIS, and the database are mainly used within the business. Some of my tables are quite large (9,000,000 rows of polygon data) but there isn't too much in the way of complex joins or complex spatial queries. The machine will also have apache and probably GeoServer as well. 1) Use an old NAS drive (about 4 or 5 years old). Windows Storage Server 2003 R2 Intel Xeon CPU 5150@2.66GHz 1GB RAM 1TB HD This machine would only be used for PostGIS and storing Raster files for access on the file server. 2) Run PostGIS on a brand new rendering machine Intel 3960x Extreme edition 6 core processor LGA2011 motherboard 16gb Quad Channel ram ATI FirePro V5900 2Gb 1.5TB 6Gb/s HD DVD/CD Windows 7 64bit This machine will be also used to render 3D Max work, so I would be sharing this machine with others. Can anyone comment on which of these would be better for me to use? I am concerned that whilst rendering is taking place on the render machine it will slow PostGIS down a lot. Is this the case? Regards, James James Stott BSc (Hons) MSc | Senior Professional Nicholas Pearson Associates | 30 Brock Street | Bath | BA1 2LN T: 01225 445548 | M: - http://www.npaconsult.co.uk/ http://www.npaconsult.co.uk/http://www.npaconsult.co.uk/about-news.asp http://www.npaconsult.co.uk/terms.asp This Electronic Transmission is intended only for the attention of the addressee. It may contain privileged and confidential information. If you are not the person for whom it is intended, you must not copy, distribute or take any action in reliance on it. If you have received this electronic transmission in error please notify us immediately by telephone, delete the transmission and destroy any hard copies. Nicholas Pearson Associates has used all reasonable efforts to ensure that this message and any attachments are free from viruses. Nicholas Pearson Associates, 30 Brock Street, Bath, BA1 2LN , Tel: +44 (0) 01225 445548, Fax: +44 (0) 01225 312387, Website: http://www.npaconsult.co.uk/ ___ 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] split donut polygons
Hi all, I have a application that cannot render donut in polygons (renders the outer ring only). As a result I need to remove the interior rings by creating a connection between the donut and the exterior ring. FME has a tool called DonutBridgeBuilder that will do this however I am having issues with the postgis reader / writer example: http://evangelism.safe.com/wp-content/uploads/2008/06/donutbuilder.jpg ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] split donut polygons
Hallo If I understand that DonutBridgeBuilder picture right, what it does is making corridors to make all interior rings being part of the outer ring. That seems like a very bad idea and will most likely result in invalid geometries in many cases. Invalid because they are self intersecting. What is the error message you get? If you cannot make the application handle holes in polygons I would suggest putting the holes in a layer above the main polygon instead. You will get a problem if you need to see what is behind the donut polygon through the holes, but then I think you will have to deal with the source to the problems. The application not handling holes. Regards Nicklas . On Wed, 2012-02-15 at 09:07 -0500, Travis Kirstine wrote: Hi all, I have a application that cannot render donut in polygons (renders the outer ring only). As a result I need to remove the interior rings by creating a connection between the donut and the exterior ring. FME has a tool called DonutBridgeBuilder that will do this however I am having issues with the postgis reader / writer example: http://evangelism.safe.com/wp-content/uploads/2008/06/donutbuilder.jpg ___ 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 can i interchange(swap) the values of a band in postgis raster
Try something like ST_AddBand(ST_Band(rast, 2), rast, 1) and have a look at the documentation: http://postgis.refractions.net/documentation/manual-svn/RT_reference.html -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Wednesday, February 15, 2012 4:34 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] How can i interchange(swap) the values of a band in postgis raster How can i interchange(swap) the values of a band in postgis raster. for example if there are 4 raster bands in the raster table and suppose if if i want to interchange the value of band1 with band2, how do i do it i did not find any such specific function in postgis 2.0 ...can it be done using ST_SetValue??? -- View this message in context: http://postgis.17.n6.nabble.com/How-can-i- interchange-swap-the-values-of-a-band-in-postgis-raster- tp4471437p4471437.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] unable to process convert *.rrd file into postgis 2.o table
rrd files are overviews for Erdas Imagine files. Use the Imagine files and specify the overview factors needed. http://www.gdal.org/frmt_hfa.html On Wed, Feb 15, 2012 at 7:05 AM, Pierre Racine pierre.rac...@sbf.ulaval.ca wrote: rrd are overview files right? Can gdalinfo read it? -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Wednesday, February 15, 2012 6:54 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] unable to process convert *.rrd file into postgis 2.o table zelio@zelio-desktop:~$ /usr/lib/postgresql/9.1/bin/raster2pgsql -s 4236 -I -C -M /home/zelio/Desktop/Zelio/Zelio/Image/27_12_new2.rrd -F -t 100x100 public.ClassifyTable | psql -d mydb Processing 1/1: /home/zelio/Desktop/Zelio/Zelio/Image/27_12_new2.rrd ERROR 1: Unable to open /home/zelio/Desktop/Zelio/Zelio/Image/27_12_new2.rrd, it has zero usable bands. ERROR: convert_raster: Could not open raster: /home/zelio/Desktop/Zelio/Zelio/Image/27_12_new2.rrd ERROR: process_rasters: Could not process raster: /home/zelio/Desktop/Zelio/Zelio/Image/27_12_new2.rrd ERROR: Unable to process rasters -- View this message in context: http://postgis.17.n6.nabble.com/unable-to- process-convert-rrd-file-into-postgis-2-o-table-tp4471790p4471790.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 -- Bborie Park Programmer Center for Vectorborne Diseases UC Davis 530-752-8380 bkp...@ucdavis.edu ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] [PostGIS] Make error in building PostGIS 2.0
I did configure with the -w flag as stated in the instruction; json configure, make, and make install ran fine. However, the same error message error when I try to make PostGIS. Do you need to copy everything in json-rel to the postresql install folder? from /c/projects/json/rel-9.0 to /c/projects/pg/pg91/ the instruction does not say this step, but I did it. - Edison Tam NGC SWE US -- View this message in context: http://postgis.17.n6.nabble.com/PostGIS-Make-error-in-building-PostGIS-2-0-tp4470575p4472505.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] HOW to do CLASSIFICATION OF A IMAGE in POSTGIS 2.0???
Check ST_Reclass(): http://postgis.refractions.net/documentation/manual-svn/RT_ST_Reclass.html -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Wednesday, February 15, 2012 7:16 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] HOW to do CLASSIFICATION OF A IMAGE in POSTGIS 2.0??? HOW to do CLASSIFICATION OF A IMAGE in POSTGIS 2.0??? -- View this message in context: http://postgis.17.n6.nabble.com/HOW-to-do- CLASSIFICATION-OF-A-IMAGE-in-POSTGIS-2-0-tp4471836p4471836.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] given the 4bands , How can i create the FCC(false color composite) from that 4 bands
Use a mixture of ST_Band, ST_AddBand and ST_Reclass http://postgis.refractions.net/documentation/manual-svn/RT_reference.html -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Wednesday, February 15, 2012 1:20 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] given the 4bands , How can i create the FCC(false color composite) from that 4 bands Hi can anybody tell me given the 4bands , How can i create the FCC(false color composite) from that 4 bands Q1. Is there any stored procedure / or function to do that diectly just by assigning parameters? or Q2. Do i have to write a query for that? greatly appreciate if anybody can help me. -- View this message in context: http://postgis.17.n6.nabble.com/given-the- 4bands-How-can-i-create-the-FCC-false-color-composite-from-that-4-bands- tp4471075p4471075.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] advice for quicker display?
OK, I am trying to use the ST_simplify command but I'm not doing it right. I am using a layer called parcels, it has columns gid and the_geom, this is the DATA line from my MapServer map file: DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING SRID=26919 USING Unique gid What am I doing wrong here? From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Juan Marín Otero Sent: Tuesday, February 14, 2012 3:13 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] advice for quicker display? You can find the documentation here. Also look at ST_SimplifyPreserveTopology, might be better depending on how strict you are with how your data gets modified. http://postgis.org/docs/ST_Simplify.html On Tue, Feb 14, 2012 at 3:09 PM, Bistrais, Bob bob.bistr...@maine.gov wrote: Wow! Where are the ST_Simplify and other functions docuemented- in PostGIS docs or MapServer docs? From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of pcr...@pcreso.com Sent: Tuesday, February 14, 2012 2:44 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] advice for quicker display? Also, In Postgis you can use the ST_Simplfy() function in mapserver zoom (scale dependent) layers. So you have one dataset, and as you zoom out you display features represented by progressively fewer vertices. You don't need every headland of a global coastline dataset plotted until you until you zoom in. This can give vastly better performance than a shapefile when zoomed out. You can use pre-prepared geometries, so each record has more than one geometry column, or you can do the point reduction on the fly (in the SQL command in the mapfile DATA statement), whichever meets your needs. The default Postgres configuration is for pretty basic hardware. You might find run pgtune, or look at the postgres admin docs to configure it to more effectively use the available memory, and see how to use tablespaces to manage data indexes across filesystems to increase overall disk throughput. So it takes some work, but overall, Postgis provides spatial data management tools with substantial performance optimisation capabilities. But you need to use them. For plotting an entire unsimplified dataset, the overhead of a database will always be somewhat slower than a file streaming from disk. Note that on good hardware with an well optimised database even this difference can be minimised. Cheers, Brent Wood --- On Wed, 2/15/12, Stephen Woodbridge wood...@swoodbridge.com wrote: From: Stephen Woodbridge wood...@swoodbridge.com Subject: Re: [postgis-users] advice for quicker display? To: postgis-users@postgis.refractions.net Date: Wednesday, February 15, 2012, 8:14 AM On 2/14/2012 1:57 PM, Bistrais, Bob wrote: As some of you already know, I'm a newbie to PostGIS and have just barely got a test PostGIS database up and running. So, my latest question is in regards to draw times. I created a statewide parcel layer from a shapefile. At statewide scale, when the application loads, the parcel data draws far slower than the original shapefile did. How can I tune this so that draw times are better? If you are drawing all the data, it will always be slower than shapefiles. The trick is to not draw more than is reasonable at a given scale. So the answer is don't try to draw all the parcels for the whole state. As you zoom in and need to only draw a subset of the parcels, you will start to get speed advantages by using the spatial (GIST) indexes in postgis. create index mytable_the_geom_gidx on mytable using gist (the_geom); -Steve W ___ postgis-users mailing list postgis-users@postgis.refractions.net http://mc/compose?to=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 -- Juan Marín Otero GIS Consultant ---Visita mi blog en- http://guachintoneando.blogspot.com --- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] split donut polygons
Thanks Nicklas I do agree that this is a bad idea. I have checked the resultant geometries and they are invalid (using FME) Original POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) Results POLYGON((0 0,4 0,2 1,1 1,1 2,2 2,2 1,4 0,4 4,0 4,0 0)) Unfortunately, I cannot alter the application to render the donuts correctly. I have consider extracting the interior rings as polygons and stacking them but as you had mentioned but this becomes very complicated (island within lakes within islands etc...) Although the bridge building method would result in invalid geometries it seems to work for this purpose (display only). If anyone has any hints on how to build this query to create bad polygons : ) they would be appreciated Hallo If I understand that DonutBridgeBuilder picture right, what it does is making corridors to make all interior rings being part of the outer ring. That seems like a very bad idea and will most likely result in invalid geometries in many cases. Invalid because they are self intersecting. What is the error message you get? If you cannot make the application handle holes in polygons I would suggest putting the holes in a layer above the main polygon instead. You will get a problem if you need to see what is behind the donut polygon through the holes, but then I think you will have to deal with the source to the problems. The application not handling holes. Regards Nicklas . On Wed, 2012-02-15 at 09:07 -0500, Travis Kirstine wrote: Hi all, I have a application that cannot render donut in polygons (renders the outer ring only). As a result I need to remove the interior rings by creating a connection between the donut and the exterior ring. FME has a tool called DonutBridgeBuilder that will do this however I am having issues with the postgis reader / writer example: http://evangelism.safe.com/wp-content/uploads/2008/06/donutbuilder.jpg ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] split donut polygons
On Wed, Feb 15, 2012 at 11:17:49AM -0500, Travis Kirstine wrote: Thanks Nicklas I do agree that this is a bad idea. I have checked the resultant geometries and they are invalid (using FME) Original POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1)) Results POLYGON((0 0,4 0,2 1,1 1,1 2,2 2,2 1,4 0,4 4,0 4,0 0)) Unfortunately, I cannot alter the application to render the donuts correctly. I have consider extracting the interior rings as polygons and stacking them but as you had mentioned but this becomes very complicated (island within lakes within islands etc...) Although the bridge building method would result in invalid geometries it seems to work for this purpose (display only). If anyone has any hints on how to build this query to create bad polygons : ) they would be appreciated 1) Dump the rings of your polygon 2) Find closest line between shell (path = 0?) to each of the holes 3) Add all rings boundaries + the lines found in (2) to a collection 4) Linemerge the collection of multilines 5) Verify the result is a single, closed line 6) Realize the resulting line is not simple (just for fun) 7) Pass the resulting line to ST_MakePoly --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] advice for quicker display?
I tried this, but replacing mytable with parcels, it didn't draw, the MapServer error log says Query error. Error declaring cursor: ERROR: column gid does not exist LINE 1: ...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text ... -although the column gid does exist. -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] advice for quicker display? DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom from mytable) as foo USING SRID=26919 USING Unique gid On 2/15/2012 11:03 AM, Bistrais, Bob wrote: OK, I am trying to use the ST_simplify command but I'm not doing it right. I am using a layer called parcels, it has columns gid and the_geom, this is the DATA line from my MapServer map file: DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING SRID=26919 USING Unique gid What am I doing wrong here? *From: * postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] advice for quicker display? You can find the documentation here. Also look at ST_SimplifyPreserveTopology, might be better depending on how strict you are with how your data gets modified. http://postgis.org/docs/ST_Simplify.html On Tue, Feb 14, 2012 at 3:09 PM, Bistrais, Bob bob.bistr...@maine.gov mailto:bob.bistr...@maine.gov wrote: Wow! Where are the ST_Simplify and other functions docuemented- in PostGIS docs or MapServer docs? *From: * postgis-users-boun...@postgis.refractions.net mailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of *pcr...@pcreso.com mailto:pcr...@pcreso.com *Sent:* Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] advice for quicker display? Also, In Postgis you can use the ST_Simplfy() function in mapserver zoom (scale dependent) layers. So you have one dataset, and as you zoom out you display features represented by progressively fewer vertices. You don't need every headland of a global coastline dataset plotted until you until you zoom in. This can give vastly better performance than a shapefile when zoomed out. You can use pre-prepared geometries, so each record has more than one geometry column, or you can do the point reduction on the fly (in the SQL command in the mapfile DATA statement), whichever meets your needs. The default Postgres configuration is for pretty basic hardware. You might find run pgtune, or look at the postgres admin docs to configure it to more effectively use the available memory, and see how to use tablespaces to manage data indexes across filesystems to increase overall disk throughput. So it takes some work, but overall, Postgis provides spatial data management tools with substantial performance optimisation capabilities. But you need to use them. For plotting an entire unsimplified dataset, the overhead of a database will always be somewhat slower than a file streaming from disk. Note that on good hardware with an well optimised database even this difference can be minimised. Cheers, Brent Wood --- On *Wed, 2/15/12, Stephen Woodbridge /wood...@swoodbridge.com mailto:wood...@swoodbridge.com/* wrote: From: Stephen Woodbridge wood...@swoodbridge.com mailto:wood...@swoodbridge.com Subject: Re: [postgis-users] advice for quicker display? To: postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net Date: Wednesday, February 15, 2012, 8:14 AM On 2/14/2012 1:57 PM, Bistrais, Bob wrote: As some of you already know, I'm a newbie to PostGIS and have just barely got a test PostGIS database up and running. So, my latest question is in regards to draw times. I created a statewide parcel layer from a shapefile. At statewide scale, when the application loads, the parcel data draws far slower than the original shapefile did. How can I tune this so that draw times are better? If you are drawing all the data, it will always be slower than shapefiles. The trick is to not draw more than is reasonable at a given scale. So the answer is don't try to draw all the parcels for the whole state. As you zoom in and need to only draw a subset of the parcels, you will start to get speed advantages by using the spatial (GIST) indexes in postgis. create index mytable_the_geom_gidx on mytable using gist (the_geom); -Steve W ___ postgis-users mailing list postgis-users@postgis.refractions.net
Re: [postgis-users] advice for quicker display?
On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote: I tried this, but replacing mytable with parcels, it didn't draw, the MapServer error log says Query error. Error declaring cursor: ERROR: column gid does not exist LINE 1: ...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text ... -although the column gid does exist. You need to select it in the subquery: DATA the_geom from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from mytable) as foo USING SRID=26919 USING Unique gid --strk; -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] advice for quicker display? DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom from mytable) as foo USING SRID=26919 USING Unique gid On 2/15/2012 11:03 AM, Bistrais, Bob wrote: OK, I am trying to use the ST_simplify command but I'm not doing it right. I am using a layer called parcels, it has columns gid and the_geom, this is the DATA line from my MapServer map file: DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING SRID=26919 USING Unique gid What am I doing wrong here? *From: * postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] advice for quicker display? You can find the documentation here. Also look at ST_SimplifyPreserveTopology, might be better depending on how strict you are with how your data gets modified. http://postgis.org/docs/ST_Simplify.html On Tue, Feb 14, 2012 at 3:09 PM, Bistrais, Bob bob.bistr...@maine.gov mailto:bob.bistr...@maine.gov wrote: Wow! Where are the ST_Simplify and other functions docuemented- in PostGIS docs or MapServer docs? *From: * postgis-users-boun...@postgis.refractions.net mailto:postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of *pcr...@pcreso.com mailto:pcr...@pcreso.com *Sent:* Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users Discussion *Subject:* Re: [postgis-users] advice for quicker display? Also, In Postgis you can use the ST_Simplfy() function in mapserver zoom (scale dependent) layers. So you have one dataset, and as you zoom out you display features represented by progressively fewer vertices. You don't need every headland of a global coastline dataset plotted until you until you zoom in. This can give vastly better performance than a shapefile when zoomed out. You can use pre-prepared geometries, so each record has more than one geometry column, or you can do the point reduction on the fly (in the SQL command in the mapfile DATA statement), whichever meets your needs. The default Postgres configuration is for pretty basic hardware. You might find run pgtune, or look at the postgres admin docs to configure it to more effectively use the available memory, and see how to use tablespaces to manage data indexes across filesystems to increase overall disk throughput. So it takes some work, but overall, Postgis provides spatial data management tools with substantial performance optimisation capabilities. But you need to use them. For plotting an entire unsimplified dataset, the overhead of a database will always be somewhat slower than a file streaming from disk. Note that on good hardware with an well optimised database even this difference can be minimised. Cheers, Brent Wood --- On *Wed, 2/15/12, Stephen Woodbridge /wood...@swoodbridge.com mailto:wood...@swoodbridge.com/* wrote: From: Stephen Woodbridge wood...@swoodbridge.com mailto:wood...@swoodbridge.com Subject: Re: [postgis-users] advice for quicker display? To: postgis-users@postgis.refractions.net mailto:postgis-users@postgis.refractions.net Date: Wednesday, February 15, 2012, 8:14 AM On 2/14/2012 1:57 PM, Bistrais, Bob wrote: As some of you already know, I'm a newbie to PostGIS and have just barely got a test PostGIS database up and running. So, my latest question is in regards to draw times. I created a statewide parcel layer from a shapefile. At statewide scale, when the application loads, the parcel data draws far slower than the original shapefile did. How can I tune this so that draw times are better? If you are drawing all the data, it will always be slower than shapefiles. The trick is to not draw more than is reasonable at a given scale. So the answer is don't try to draw all the parcels for the whole state. As you zoom in and need to only draw a subset of the
Re: [postgis-users] advice for quicker display?
Interesting- that works until I zoom in to about 1:5 scale, then the layer
stops drawing with this error:
prepare_database(): Query error. Error declaring cursor: ERROR: column
map_bk_lot does not exist
LINE 1: DECLARE mycursor BINARY CURSOR FOR SELECT map_bk_lot::text...
^
With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
map_bk_lot::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from parcels) as foo
WHERE the_geom setSRID('BOX3D(438251.727851473
4907493.5454546,445395.272148527 4910840.4545454)'::BOX3D, 26919 )
---Once again, the map_bk_lot column does indeed exist. It will redraw fine
when I zoom out again.
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Sandro
Santilli
Sent: Wednesday, February 15, 2012 2:50 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] advice for quicker display?
On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote:
I tried this, but replacing mytable with parcels, it didn't draw, the
MapServer error log says Query error. Error declaring cursor: ERROR: column
gid does not exist
LINE 1: ...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text ...
-although the column gid does exist.
You need to select it in the subquery:
DATA the_geom from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from
mytable) as foo USING SRID=26919 USING Unique gid
--strk;
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Wednesday, February 15, 2012 12:26 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] advice for quicker display?
DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom from
mytable) as foo USING SRID=26919 USING Unique gid
On 2/15/2012 11:03 AM, Bistrais, Bob wrote:
OK, I am trying to use the ST_simplify command but I'm not doing it
right. I am using a layer called parcels, it has columns gid and
the_geom, this is the DATA line from my MapServer map file:
DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING SRID=26919
USING Unique gid
What am I doing wrong here?
*From: * postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of
*Juan Marín Otero
*Sent:* Tuesday, February 14, 2012 3:13 PM
*To:* PostGIS Users Discussion
*Subject:* Re: [postgis-users] advice for quicker display?
You can find the documentation here. Also look at
ST_SimplifyPreserveTopology, might be better depending on how strict you
are with how your data gets modified.
http://postgis.org/docs/ST_Simplify.html
On Tue, Feb 14, 2012 at 3:09 PM, Bistrais, Bob bob.bistr...@maine.gov
mailto:bob.bistr...@maine.gov wrote:
Wow! Where are the ST_Simplify and other functions docuemented- in
PostGIS docs or MapServer docs?
*From: * postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf Of
*pcr...@pcreso.com mailto:pcr...@pcreso.com
*Sent:* Tuesday, February 14, 2012 2:44 PM
*To:* PostGIS Users Discussion
*Subject:* Re: [postgis-users] advice for quicker display?
Also,
In Postgis you can use the ST_Simplfy() function in mapserver zoom
(scale dependent) layers. So you have one dataset, and as you zoom out
you display features represented by progressively fewer vertices. You
don't need every headland of a global coastline dataset plotted until
you until you zoom in. This can give vastly better performance than a
shapefile when zoomed out.
You can use pre-prepared geometries, so each record has more than one
geometry column, or you can do the point reduction on the fly (in the
SQL command in the mapfile DATA statement), whichever meets your needs.
The default Postgres configuration is for pretty basic hardware. You
might find run pgtune, or look at the postgres admin docs to configure
it to more effectively use the available memory, and see how to use
tablespaces to manage data indexes across filesystems to increase
overall disk throughput.
So it takes some work, but overall, Postgis provides spatial data
management tools with substantial performance optimisation capabilities.
But you need to use them.
For plotting an entire unsimplified dataset, the overhead of a database
will always be somewhat slower than a file streaming from disk. Note
that on good hardware with an well optimised database even this
difference can be minimised.
Cheers,
Brent Wood
--- On *Wed, 2/15/12, Stephen Woodbridge
Re: [postgis-users] advice for quicker display?
Actually it does NOT exist in the sub-query. Mapserver modifies the
query because it needs the label text. So you sub-query needs to have
ALL potential columns that you might need explicitly referenced.
DATA the_geom from (select gid, map_bk_lot, ST_Simplify(the_geom, 10.0)
as the_geom from mytable) as foo USING SRID=26919 USING Unique gid
-Steve W
On 2/15/2012 2:58 PM, Bistrais, Bob wrote:
Interesting- that works until I zoom in to about 1:5 scale, then
the layer stops drawing with this error:
prepare_database(): Query error. Error declaring cursor: ERROR:
column map_bk_lot does not exist LINE 1: DECLARE mycursor BINARY
CURSOR FOR SELECT map_bk_lot::text... ^
With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
map_bk_lot::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from
parcels) as foo WHERE the_geom setSRID('BOX3D(438251.727851473
4907493.5454546,445395.272148527 4910840.4545454)'::BOX3D, 26919 )
---Once again, the map_bk_lot column does indeed exist. It will
redraw fine when I zoom out again.
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Sandro Santilli Sent: Wednesday, February 15, 2012 2:50 PM To:
PostGIS Users Discussion Subject: Re: [postgis-users] advice for
quicker display?
On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote:
I tried this, but replacing mytable with parcels, it didn't draw,
the MapServer error log says Query error. Error declaring cursor:
ERROR: column gid does not exist LINE 1:
...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text
...
-although the column gid does exist.
You need to select it in the subquery:
DATA the_geom from (select gid, ST_Simplify(the_geom, 10.0) as
the_geom from mytable) as foo USING SRID=26919 USING Unique gid
--strk;
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To:
postgis-users@postgis.refractions.net Subject: Re: [postgis-users]
advice for quicker display?
DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom
from mytable) as foo USING SRID=26919 USING Unique gid
On 2/15/2012 11:03 AM, Bistrais, Bob wrote:
OK, I am trying to use the ST_simplify command but I'm not doing
it right. I am using a layer called parcels, it has columns gid
and the_geom, this is the DATA line from my MapServer map file:
DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING
SRID=26919 USING Unique gid
What am I doing wrong here?
*From: * postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf
Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM
*To:* PostGIS Users Discussion *Subject:* Re: [postgis-users]
advice for quicker display?
You can find the documentation here. Also look at
ST_SimplifyPreserveTopology, might be better depending on how
strict you are with how your data gets modified.
http://postgis.org/docs/ST_Simplify.html
On Tue, Feb 14, 2012 at 3:09 PM, Bistrais,
Bobbob.bistr...@maine.gov mailto:bob.bistr...@maine.gov
wrote:
Wow! Where are the ST_Simplify and other functions docuemented-
in PostGIS docs or MapServer docs?
*From: * postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net] *On
Behalf Of *pcr...@pcreso.commailto:pcr...@pcreso.com *Sent:*
Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users
Discussion *Subject:* Re: [postgis-users] advice for quicker
display?
Also,
In Postgis you can use the ST_Simplfy() function in mapserver
zoom (scale dependent) layers. So you have one dataset, and as
you zoom out you display features represented by progressively
fewer vertices. You don't need every headland of a global
coastline dataset plotted until you until you zoom in. This can
give vastly better performance than a shapefile when zoomed out.
You can use pre-prepared geometries, so each record has more than
one geometry column, or you can do the point reduction on the fly
(in the SQL command in the mapfile DATA statement), whichever
meets your needs.
The default Postgres configuration is for pretty basic hardware.
You might find run pgtune, or look at the postgres admin docs
to configure it to more effectively use the available memory, and
see how to use tablespaces to manage data indexes across
filesystems to increase overall disk throughput.
So it takes some work, but overall, Postgis provides spatial
data management tools with substantial performance optimisation
capabilities. But you need to use them.
For plotting an entire unsimplified dataset, the overhead of a
database will always be somewhat slower than a
Re: [postgis-users] advice for quicker display?
OK, that seems to work by adding the map_bk_lot field. If I needed to add more
fields to the subquery, would I simply add them in with commas separating them?
Example:
(select gid, map_bk_lot, another_field1, another_field2, ST_Simplify(the_geom,
10.0)
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Wednesday, February 15, 2012 3:06 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] advice for quicker display?
Actually it does NOT exist in the sub-query. Mapserver modifies the
query because it needs the label text. So you sub-query needs to have
ALL potential columns that you might need explicitly referenced.
DATA the_geom from (select gid, map_bk_lot, ST_Simplify(the_geom, 10.0)
as the_geom from mytable) as foo USING SRID=26919 USING Unique gid
-Steve W
On 2/15/2012 2:58 PM, Bistrais, Bob wrote:
Interesting- that works until I zoom in to about 1:5 scale, then
the layer stops drawing with this error:
prepare_database(): Query error. Error declaring cursor: ERROR:
column map_bk_lot does not exist LINE 1: DECLARE mycursor BINARY
CURSOR FOR SELECT map_bk_lot::text... ^
With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
map_bk_lot::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from
parcels) as foo WHERE the_geom setSRID('BOX3D(438251.727851473
4907493.5454546,445395.272148527 4910840.4545454)'::BOX3D, 26919 )
---Once again, the map_bk_lot column does indeed exist. It will
redraw fine when I zoom out again.
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Sandro Santilli Sent: Wednesday, February 15, 2012 2:50 PM To:
PostGIS Users Discussion Subject: Re: [postgis-users] advice for
quicker display?
On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote:
I tried this, but replacing mytable with parcels, it didn't draw,
the MapServer error log says Query error. Error declaring cursor:
ERROR: column gid does not exist LINE 1:
...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text
...
-although the column gid does exist.
You need to select it in the subquery:
DATA the_geom from (select gid, ST_Simplify(the_geom, 10.0) as
the_geom from mytable) as foo USING SRID=26919 USING Unique gid
--strk;
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To:
postgis-users@postgis.refractions.net Subject: Re: [postgis-users]
advice for quicker display?
DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom
from mytable) as foo USING SRID=26919 USING Unique gid
On 2/15/2012 11:03 AM, Bistrais, Bob wrote:
OK, I am trying to use the ST_simplify command but I'm not doing
it right. I am using a layer called parcels, it has columns gid
and the_geom, this is the DATA line from my MapServer map file:
DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING
SRID=26919 USING Unique gid
What am I doing wrong here?
*From: * postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf
Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM
*To:* PostGIS Users Discussion *Subject:* Re: [postgis-users]
advice for quicker display?
You can find the documentation here. Also look at
ST_SimplifyPreserveTopology, might be better depending on how
strict you are with how your data gets modified.
http://postgis.org/docs/ST_Simplify.html
On Tue, Feb 14, 2012 at 3:09 PM, Bistrais,
Bobbob.bistr...@maine.gov mailto:bob.bistr...@maine.gov
wrote:
Wow! Where are the ST_Simplify and other functions docuemented-
in PostGIS docs or MapServer docs?
*From: * postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net] *On
Behalf Of *pcr...@pcreso.commailto:pcr...@pcreso.com *Sent:*
Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users
Discussion *Subject:* Re: [postgis-users] advice for quicker
display?
Also,
In Postgis you can use the ST_Simplfy() function in mapserver
zoom (scale dependent) layers. So you have one dataset, and as
you zoom out you display features represented by progressively
fewer vertices. You don't need every headland of a global
coastline dataset plotted until you until you zoom in. This can
give vastly better performance than a shapefile when zoomed out.
You can use pre-prepared geometries, so each record has more than
one geometry column, or you can do the point reduction on the fly
(in the SQL command
Re: [postgis-users] advice for quicker display?
Yes, that is the way it works.
On 2/15/2012 3:16 PM, Bistrais, Bob wrote:
OK, that seems to work by adding the map_bk_lot field. If I needed to add more
fields to the subquery, would I simply add them in with commas separating them?
Example:
(select gid, map_bk_lot, another_field1, another_field2, ST_Simplify(the_geom,
10.0)
-Original Message-
From: postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen
Woodbridge
Sent: Wednesday, February 15, 2012 3:06 PM
To: postgis-users@postgis.refractions.net
Subject: Re: [postgis-users] advice for quicker display?
Actually it does NOT exist in the sub-query. Mapserver modifies the
query because it needs the label text. So you sub-query needs to have
ALL potential columns that you might need explicitly referenced.
DATA the_geom from (select gid, map_bk_lot, ST_Simplify(the_geom, 10.0)
as the_geom from mytable) as foo USING SRID=26919 USING Unique gid
-Steve W
On 2/15/2012 2:58 PM, Bistrais, Bob wrote:
Interesting- that works until I zoom in to about 1:5 scale, then
the layer stops drawing with this error:
prepare_database(): Query error. Error declaring cursor: ERROR:
column map_bk_lot does not exist LINE 1: DECLARE mycursor BINARY
CURSOR FOR SELECT map_bk_lot::text... ^
With query string: DECLARE mycursor BINARY CURSOR FOR SELECT
map_bk_lot::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from (select gid, ST_Simplify(the_geom, 10.0) as the_geom from
parcels) as foo WHERE the_geom setSRID('BOX3D(438251.727851473
4907493.5454546,445395.272148527 4910840.4545454)'::BOX3D, 26919 )
---Once again, the map_bk_lot column does indeed exist. It will
redraw fine when I zoom out again.
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Sandro Santilli Sent: Wednesday, February 15, 2012 2:50 PM To:
PostGIS Users Discussion Subject: Re: [postgis-users] advice for
quicker display?
On Wed, Feb 15, 2012 at 02:26:19PM -0500, Bistrais, Bob wrote:
I tried this, but replacing mytable with parcels, it didn't draw,
the MapServer error log says Query error. Error declaring cursor:
ERROR: column gid does not exist LINE 1:
...inary(force_collection(force_2d(the_geom)),'NDR'),gid::text
...
-although the column gid does exist.
You need to select it in the subquery:
DATA the_geom from (select gid, ST_Simplify(the_geom, 10.0) as
the_geom from mytable) as foo USING SRID=26919 USING Unique gid
--strk;
-Original Message- From:
postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of
Stephen Woodbridge Sent: Wednesday, February 15, 2012 12:26 PM To:
postgis-users@postgis.refractions.net Subject: Re: [postgis-users]
advice for quicker display?
DATA the_geom from (select ST_Simplify(the_geom, 10.0) as the_geom
from mytable) as foo USING SRID=26919 USING Unique gid
On 2/15/2012 11:03 AM, Bistrais, Bob wrote:
OK, I am trying to use the ST_simplify command but I'm not doing
it right. I am using a layer called parcels, it has columns gid
and the_geom, this is the DATA line from my MapServer map file:
DATA the_geom from (ST_Simplify(the_geom, 10.0)) USING
SRID=26919 USING Unique gid
What am I doing wrong here?
*From: * postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net] *On Behalf
Of *Juan Marín Otero *Sent:* Tuesday, February 14, 2012 3:13 PM
*To:* PostGIS Users Discussion *Subject:* Re: [postgis-users]
advice for quicker display?
You can find the documentation here. Also look at
ST_SimplifyPreserveTopology, might be better depending on how
strict you are with how your data gets modified.
http://postgis.org/docs/ST_Simplify.html
On Tue, Feb 14, 2012 at 3:09 PM, Bistrais,
Bobbob.bistr...@maine.govmailto:bob.bistr...@maine.gov
wrote:
Wow! Where are the ST_Simplify and other functions docuemented-
in PostGIS docs or MapServer docs?
*From: * postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net
[mailto:postgis-users-boun...@postgis.refractions.net
mailto:postgis-users-boun...@postgis.refractions.net] *On
Behalf Of *pcr...@pcreso.commailto:pcr...@pcreso.com *Sent:*
Tuesday, February 14, 2012 2:44 PM *To:* PostGIS Users
Discussion *Subject:* Re: [postgis-users] advice for quicker
display?
Also,
In Postgis you can use the ST_Simplfy() function in mapserver
zoom (scale dependent) layers. So you have one dataset, and as
you zoom out you display features represented by progressively
fewer vertices. You don't need every headland of a global
coastline dataset plotted until you until you zoom in. This can
give vastly better performance than a shapefile when zoomed out.
You can use pre-prepared geometries, so each record has more than
one geometry column, or you can do the point reduction on the fly
(in the SQL
[postgis-users] importing shapefile error
zelio@zelio-desktop:~$ /usr/lib/postgresql/9.1/bin/shp2pgsql -s 4326 -i -I
/home/zelio/Desktop/Final(2)/FOREST.shp public.FOREST | psql -d prakasam
bash: syntax error near unexpected token `('
zelio@zelio-desktop:~$
--
View this message in context:
http://postgis.17.n6.nabble.com/importing-shapefile-error-tp4473974p4473974.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] importing shapefile error
On 16 February 2012 10:17, Zelio Fernandes zelio.f...@gmail.com wrote:
zelio@zelio-desktop:~$ /usr/lib/postgresql/9.1/bin/shp2pgsql -s 4326 -i -I
/home/zelio/Desktop/Final(2)/FOREST.shp public.FOREST | psql -d prakasam
bash: syntax error near unexpected token `('
zelio@zelio-desktop:~$
This has nothing to do with PostGIS; it is a syntax error in BASH due
the '(' and ')'. The normal practice to naming file path arguments is
to use double quotes around files, e.g.
command some file(2).shp
More info:
http://www.gnu.org/software/bash/manual/bashref.html#Quoting
-Mike
___
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] ST_AsJpeg
Hi Regina 2012/2/14 Paragon Corporation l...@pcorp.us wrote: Here it is in the docs now: http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL Citation from there: Sadly PSQL doesn't have easy to use built-in functionality for outputting binaries... Did anyone show and discuss this issue with postgres specialists? --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
Actually just ST_Band(rast, 2,1) would do the job... -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Pierre Racine Sent: Wednesday, February 15, 2012 9:34 AM To: PostGIS Users Discussion Subject: Re: [postgis-users] How can i interchange(swap) the values of a band in postgis raster Try something like ST_AddBand(ST_Band(rast, 2), rast, 1) and have a look at the documentation: http://postgis.refractions.net/documentation/manual-svn/RT_reference.html -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- boun...@postgis.refractions.net] On Behalf Of Zelio Fernandes Sent: Wednesday, February 15, 2012 4:34 AM To: postgis-users@postgis.refractions.net Subject: [postgis-users] How can i interchange(swap) the values of a band in postgis raster How can i interchange(swap) the values of a band in postgis raster. for example if there are 4 raster bands in the raster table and suppose if if i want to interchange the value of band1 with band2, how do i do it i did not find any such specific function in postgis 2.0 ...can it be done using ST_SetValue??? -- View this message in context: http://postgis.17.n6.nabble.com/How-can-i- interchange-swap-the-values-of-a-band-in-postgis-raster- tp4471437p4471437.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 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
How to get all the attribute values of a classified image into postgis 2.0? it only shows me the columns of raster table as Rid,Rast,file whereas the columns O_id,value,count,Red,Green,Blue,opacity,classname are not shown as columns? PLEASE NOTE THAT FOR THIS IMAGE IT SHOWS me all this columns(O_id,value,count,Red,Green,Blue,opacity,classname) IN ARCGIS but when i import this same image in postgis2.0 table ..this table has none of those columns. -- 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-tp4471437p4474138.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] How can i interchange(swap) the values of a band in postgis raster
thanks for your reply.. but if ST_Band(rast,2,1) means that i am assigning the value of 2nd raster band to 1st..will it create a new raster with this...i tried this ...but i m not sure whether it creates new raster or it replaces values in same raster. -- 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-tp4471437p4474144.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] How can i interchange(swap) the values of a band in postgis raster
but if ST_Band(rast,2,1) means that i am assigning the value of 2nd raster band to 1st..will it create a new raster with this...i tried this ...but i m not sure whether it creates new raster or it replaces values in same raster. Most function create a new raster. If you want to save your raster back to the table you have to write an UPDATE SQL statement. Pierre ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] PostGIS Manager QGIS Plugin error
I'm just now getting set up with a PostGIS database connected with QGIS. A few plugins are giving me errors when I try to connect to my database. I am using Mac OS X 10.6.8 and QGIS 1.8. When I try to connect to my PostGIS database using the PostGIS Manager plugin, I receive the following error: *“Couldn’t connect to database: missing “=” after “‘” in connection info string”.* Any thoughts? I will add that I have successfully added PostGIS layers to my project, but now want to start using pgRouting, so need to connect to the database. I receive the exact same error when attempting to use the pgRouting Layer plugin. Thanks! Kevin ___ 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
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] postgis-2.0.0alpha5
Waiting for your delectation, http://postgis.org/download/postgis-2.0.0alpha5.tar.gz Try it, load your data into it, see how it goes... report back with a ticket. Thanks! P. ___ 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
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 : hostspecificdatabaseindividualuser machineIP/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
Re: [postgis-users] How can i interchange(swap) the values of a band in postgis raster
SELECT ST_AddBand(ST_Band(rast, 2), rast ,1) from image2tiff; --the query runs but does does interchange bandvalues not it add the new band , i verified this by this query SELECT distinct ST_Value(rast, 1,1,1) As b1pval,ST_Value(rast, 2,1,1) As b2pval, ST_Value(rast, 3,1,1) As b3pval,ST_Value(rast, 4,1,1) As b4pval FROM image2tiff; -- 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-tp4471437p4474993.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] PostGIS Manager QGIS Plugin error
Kevin, That sounds like a syntax error in the plugin itself. You probably need to contact the maintainer. I can't actually get it to work in QGIS 1.7.3 on OSX 10.6.8 cheers Ben On 16/02/2012, at 7:21 AM, Kevin Goulding wrote: I'm just now getting set up with a PostGIS database connected with QGIS. A few plugins are giving me errors when I try to connect to my database. I am using Mac OS X 10.6.8 and QGIS 1.8. When I try to connect to my PostGIS database using the PostGIS Manager plugin, I receive the following error: “Couldn’t connect to database: missing “=” after “‘” in connection info string”. Any thoughts? I will add that I have successfully added PostGIS layers to my project, but now want to start using pgRouting, so need to connect to the database. I receive the exact same error when attempting to use the pgRouting Layer plugin. Thanks! Kevin ___ 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
On Wed, Feb 15, 2012 at 10:37:08PM +0100, Stefan Keller wrote: Hi Regina 2012/2/14 Paragon Corporation l...@pcorp.us wrote: Here it is in the docs now: http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL Citation from there: Sadly PSQL doesn't have easy to use built-in functionality for outputting binaries... Did anyone show and discuss this issue with postgres specialists? I don't think there's much to discuss. I'm sure a patch to psql would be welcome. --strk; ,--o-. | __/ |Delivering high quality PostGIS 2.0 ! | / 2.0 |http://strk.keybit.net `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] PostGIS Manager QGIS Plugin error
Please try the new DB manager plugin and report back. Thanks. http://faunalia.it/pc Sent from mobile, sorry for being short - Reply message - Da: Ben Madin li...@remoteinformation.com.au A: PostGIS Users Discussion postgis-users@postgis.refractions.net Oggetto: [postgis-users] PostGIS Manager QGIS Plugin error Data: gio, feb 16, 2012 08:24 Kevin, That sounds like a syntax error in the plugin itself. You probably need to contact the maintainer. I can't actually get it to work in QGIS 1.7.3 on OSX 10.6.8 cheers Ben On 16/02/2012, at 7:21 AM, Kevin Goulding wrote:I'm just now getting set up with a PostGIS database connected with QGIS. A few plugins are giving me errors when I try to connect to my database. I am using Mac OS X 10.6.8 and QGIS 1.8. When I try to connect to my PostGIS database using the PostGIS Manager plugin, I receive the following error:“Couldn’t connect to database: missing “=” after “‘” in connection info string”. Any thoughts? I will add that I have successfully added PostGIS layers to my project, but now want to start using pgRouting, so need to connect to the database. I receive the exact same error when attempting to use the pgRouting Layer plugin. Thanks! Kevin ___ 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
