Re: [postgis-users] Odd question
Thank you, now perfectly clear. From: l...@pcorp.us To: postgis-users@postgis.refractions.net Date: Mon, 17 Sep 2012 00:35:16 -0400 Subject: Re: [postgis-users] Odd question The offset is to get you on the right side of the street, not to compensate for the fuzzyness of census data. Not sure if that was clear. The census data are all street centerlines, and what the offset is intended to do is this Say your road looks like this: 0 24 30 --- 1 2531 Say lets say your street address is like 25 State street, the interpolation would put you on the centerline of the line corresponding to 24/25 But since we know you are 25 -- the 10 offset pushes the point to hit the 25 (so 10 meters) in the 25 direction So the offset should be more accurate than without as it would put you on the correct side of the road instead of standing in the middle of it. From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Chris English Sent: Sunday, September 16, 2012 9:14 AM To: postgis_users Subject: Re: [postgis-users] Odd question But changing the setting to zero doesn't get one necessarily closer to the true location of the address, does it? It remains fuzzy as was the intent of the census bureau to elide. Chris Date: Sat, 15 Sep 2012 18:46:06 -0500 From: ericas...@gmail.com To: postgis-users@postgis.refractions.net Subject: Re: [postgis-users] Odd question That's fantastic! Exactly what I was looking for. Thank you very much. On Sat, Sep 15, 2012 at 6:44 PM, Paragon Corporation wrote: Eric, Did y ou want to change the default or set it to 0 or something? We were meaning to expose that but wasn't sure if anyone would be interested in changing it. The setting is on the function: interpolate_from_address Just change the hardcoded default of 10 to what you want. CREATE OR REPLACE FUNCTION interpolate_from_address(given_address INTEGER, in_addr1 VARCHAR, in_addr2 VARCHAR, in_road GEOMETRY, in_side VARCHAR DEFAULT '',in_offset_m float DEFAULT 10 ); In 2.1. we'll add to the list to allow this to be voerrided at the geocode function level as an additional arg. Can't do that with the 2.0 one though since that would change the api. Hope that helps, Regina http://www.postgis.us -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Eric Aspengren Sent: Saturday, September 15, 2012 5:20 PM To: PostGIS Users Discussion Subject: [postgis-users] Odd question Anybody know how to remove the default 10 meter offset from the PostGIS geocoder? -- Eric Aspengren (402) 478-8683 www.streetlevelstrategies.com ericas...@gmail.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Eric Aspengren (402) 478-8683 www.streetlevelstrategies.com ericas...@gmail.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
[postgis-users] loading raster into DB takes more then an hour
I'm loading a DEM (float, single band, 27654x17623) through raster2pgsql and it takes more then hour to complete. It happens both with tiled loading (256x256) and single block. Is it normal? giovanni ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] out of db raster path?
I've loaded an out of db raster which points to an .adf file (arcinfo binary grid). Whenever I operate on the column I obatin "rt_band_load_offline_data: Cannot open offline raster hdr.adf" Question: is the file path saved or just the file name? giovanni ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of db raster path?
I simply had to pass the full path to raster2pgsql... giovanni Sent from Nexus Il giorno 17/set/2012 18:47, "G. Allegri" ha scritto: > I've loaded an out of db raster which points to an .adf file (arcinfo > binary grid). > Whenever I operate on the column I obatin > > "rt_band_load_offline_data: Cannot open offline raster hdr.adf" > > Question: is the file path saved or just the file name? > giovanni > ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] out of db raster path?
Yep. You always need to provide the absolute path for the file. This is because there is no agnostic way (across different OSes) to determine the absolute path of a raster file from raster2pgsql. -bborie On 09/17/2012 10:55 AM, G. Allegri wrote: > I simply had to pass the full path to raster2pgsql... > > giovanni > > Sent from Nexus > Il giorno 17/set/2012 18:47, "G. Allegri" ha scritto: > >> I've loaded an out of db raster which points to an .adf file (arcinfo >> binary grid). >> Whenever I operate on the column I obatin >> >> "rt_band_load_offline_data: Cannot open offline raster hdr.adf" >> >> Question: is the file path saved or just the file name? >> giovanni >> > > > > ___ > 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] loading raster into DB takes more then an hour
Yes, no and maybe. You haven't given sufficient information about your environment to provide any informed advice. What is your raster2pgsql command? Are you piping the output from raster2pgsql directly to psql? Or loading from a file containing the output? What OS? PostgreSQL version? -bborie On 09/17/2012 09:10 AM, G. Allegri wrote: > I'm loading a DEM (float, single band, 27654x17623) through raster2pgsql > and it takes more then hour to complete. > It happens both with tiled loading (256x256) and single block. > Is it normal? > > giovanni > > > > ___ > 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] loading raster into DB takes more then an hour
And CPU and hard drive types? I get very different times on different machines for the same dataset. > -Original Message- > From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users- > boun...@postgis.refractions.net] On Behalf Of Bborie Park > Sent: Monday, September 17, 2012 2:55 PM > To: postgis-users@postgis.refractions.net > Subject: Re: [postgis-users] loading raster into DB takes more then an hour > > Yes, no and maybe. You haven't given sufficient information about your > environment to provide any informed advice. > > What is your raster2pgsql command? > > Are you piping the output from raster2pgsql directly to psql? Or > loading from a file containing the output? > > What OS? PostgreSQL version? > > -bborie > > On 09/17/2012 09:10 AM, G. Allegri wrote: > > I'm loading a DEM (float, single band, 27654x17623) through raster2pgsql > > and it takes more then hour to complete. > > It happens both with tiled loading (256x256) and single block. > > Is it normal? > > > > giovanni > > > > > > > > ___ > > 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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
I'm trying to determine if the response time we're seeing on a query is reasonable or if there's anything we can do to speed it up. Looking for advice and/or a reality check. In general, we benefit from dicing our large polygon layers to speed up intersection queries (a big thanks to Paul Ramsey for that pointer), but the catch with this particular query is that it is issued from a web-based GIS application where a user draws a (potentially very large) polygon on-the-fly and then total length calculations are made for intersecting line features within this dynamically drawn polygon. I've even tried dicing dynamically, but it seems to only add more overall time. Here is the SQL for one particular polygon I drew while testing: SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) * 0.00062137119AS miles FROM baari_streams s WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 )) GROUP BY s.legcode, s.strahler ORDER BY s.legcode, s.strahler; The explain analyze output: http://explain.depesz.com/s/PNZ The line table has 254833 records. It has a spatial index and the optimizer is using it. The index is clustered. And I have run vacuum analyze on it. So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 rows). Is this about as good as can be expected? Ideally, we'd love to see this return in about 1 second or less. Is that unreasonable? Version info: PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.8" USE_STATS Thanks for any insight you can provide. Shira ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] How to speed up a spatial intersection where the intersecting polygon is drawn on-the-fly?
Hi, I'm unsure how good ST_Transform performs. Did you consider adding a partial functional index like described in [1] ? -S. [1] http://postgis.refractions.net/documentation/manual-1.5/ST_Transform.html 2012/9/18 Shira Bezalel : > > I'm trying to determine if the response time we're seeing on a query is > reasonable or if there's anything we can do to speed it up. Looking for > advice and/or a reality check. > > In general, we benefit from dicing our large polygon layers to speed up > intersection queries (a big thanks to Paul Ramsey for that pointer), but the > catch with this particular query is that it is issued from a web-based GIS > application where a user draws a (potentially very large) polygon on-the-fly > and then total length calculations are made for intersecting line features > within this dynamically drawn polygon. I've even tried dicing dynamically, > but it seems to only add more overall time. > > Here is the SQL for one particular polygon I drew while testing: > > SELECT s.legcode, s.strahler, sum ( st_length ( st_intersection ( the_geom, > st_transform ( ST_GeomFromText ( 'POLYGON((-13648936.363952 > 4623692.0844833,-13611023.597928 4607181.686376,-13648324.867726 > 4592505.7769473,-13590844.222464 4581498.8448758,-13665446.76206 > 4572326.4014828,-13674007.709226 4607181.686376,-13653828.333762 > 4591282.7844949,-13648936.363952 4623692.0844833))', 900913 ) , 3310 ) ) ) ) > * 0.00062137119AS miles > FROM baari_streams s > WHERE ST_Intersects ( the_geom, st_transform ( ST_GeomFromText ( > 'POLYGON((-13648936.363952 4623692.0844833,-13611023.597928 > 4607181.686376,-13648324.867726 4592505.7769473,-13590844.222464 > 4581498.8448758,-13665446.76206 4572326.4014828,-13674007.709226 > 4607181.686376,-13653828.333762 4591282.7844949,-13648936.363952 > 4623692.0844833))', 900913 ) , 3310 )) > GROUP BY s.legcode, s.strahler > ORDER BY s.legcode, s.strahler; > > > > The explain analyze output: > > http://explain.depesz.com/s/PNZ > > The line table has 254833 records. It has a spatial index and the optimizer > is using it. The index is clustered. And I have run vacuum analyze on it. > > So it takes about 3.5 seconds to return 27 rows (an aggregation of 28863 > rows). Is this about as good as can be expected? Ideally, we'd love to see > this return in about 1 second or less. Is that unreasonable? > > Version info: > PostgreSQL 9.1.2 on x86_64-pc-linux-gnu, compiled by gcc-4.6.real > (Ubuntu/Linaro 4.6.1-9ubuntu3) 4.6.1, 64-bit > POSTGIS="1.5.3" GEOS="3.2.2-CAPI-1.6.2" PROJ="Rel. 4.7.1, 23 September 2009" > LIBXML="2.7.8" USE_STATS > > Thanks for any insight you can provide. > > Shira > > ___ > 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
