Re: [postgis-users] raster output
Forgot to add if you want to output a whole table or a set of records as a single raster, you'll want to also use the ST_Union function. So something like SELECT ST_AddBand(ST_Reclass(ST_Union(rast) ) FROM yourtable WHERE ST_Intersects(rast,somegeometry) http://www.postgis.org/documentation/manual-svn/RT_ST_Union.html > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Paragon Corporation > Sent: Wednesday, December 21, 2011 4:03 PM > To: 'PostGIS Users Discussion' > Subject: Re: [postgis-users] raster output > > Steve, > > Actually that was just for coloring geometries. For raster I > usually just output the raster as RGB > > R for band 1, G band 2, B for band 3 > > If you have a single band raster, you'll need to do > reclassify the bands if you want to out as PNG for example > > http://www.postgis.org/documentation/manual-svn/RT_ST_Reclass.html > > Look at the last example in the docs. > > Or you could just export as one of the gdal 1 Banded > supporting formats and use QGIS to do the classification. > > > -Original Message- > > From: postgis-users-boun...@postgis.refractions.net > > [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of > > Stephen Crawford > > Sent: Wednesday, December 21, 2011 10:51 AM > > To: PostGIS Users Discussion > > Subject: Re: [postgis-users] raster output > > > > Thanks for getting back to me. I want to do something like > #3, but I > > just don't understand the work flow. I have raw data as a one band > > raster in postGIS, and the (seemingly) simple thing i would > like to do > > is output the entire table as a jpg or png with the cells > classified > > by color. I see references to "color tables" and > "palettes". I can't > > seem to figure out where I can define "if cell val < 0.5, > color = 0, > > 0, 255" etc. > > > > Thanks, > > Steve > > > > On 12/20/2011 6:13 PM, Paragon Corporation wrote: > > > 3) You can use gdal_translate (compiled with PostgreSQL > support) to > > > output a postgis raster query. > > > > -- > > Stephen Crawford > > Center for Environmental Informatics > > The Pennsylvania State University > > > > > > > > ___ > > 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] speeding up simple pt-in-poly lookups
> Chopping polygons is pretty simple, with a grid & st_intersection() Just for interest, I posted a method for gridding/chopping polygons the other day in a set of comparative posting with SQL Server and Oracle.http://www.spatialdbadvisor.com/postgis_tips_tricks/258/gridding-a-geometric-objectThere may be faster ways of doing this, but my attempt was about functionality not speed. Also, the approach here is not optimized for a singlepolygon: it is designed to use the same grid no matter how many polygons etc are put into the chopper.regardsSimon-- Holder of "2011 Oracle Spatial Excellence Award for Education and Research."SpatialDB Advice and Design, Solutions Architecture and Programming,Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified ProfessionalOracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397Mobile: +61 418 396391Skype: sggreenerLongitude: 147.20515 (147° 12' 18" E)Latitude: -43.01530 (43° 00' 55" S)GeoHash: r22em9r98wgNAC:W80CK 7SWP3___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster output
Steve, Actually that was just for coloring geometries. For raster I usually just output the raster as RGB R for band 1, G band 2, B for band 3 If you have a single band raster, you'll need to do reclassify the bands if you want to out as PNG for example http://www.postgis.org/documentation/manual-svn/RT_ST_Reclass.html Look at the last example in the docs. Or you could just export as one of the gdal 1 Banded supporting formats and use QGIS to do the classification. > -Original Message- > From: postgis-users-boun...@postgis.refractions.net > [mailto:postgis-users-boun...@postgis.refractions.net] On > Behalf Of Stephen Crawford > Sent: Wednesday, December 21, 2011 10:51 AM > To: PostGIS Users Discussion > Subject: Re: [postgis-users] raster output > > Thanks for getting back to me. I want to do something like > #3, but I just don't understand the work flow. I have raw > data as a one band raster in postGIS, and the (seemingly) > simple thing i would like to do is output the entire table as > a jpg or png with the cells classified by color. I see > references to "color tables" and "palettes". I can't seem to > figure out where I can define "if cell val < 0.5, color = 0, > 0, 255" etc. > > Thanks, > Steve > > On 12/20/2011 6:13 PM, Paragon Corporation wrote: > > 3) You can use gdal_translate (compiled with PostgreSQL support) to > > output a postgis raster query. > > -- > Stephen Crawford > Center for Environmental Informatics > The Pennsylvania State University > > > > ___ > 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] postgis installation fail on sles11
Hi,I'm on SLES11 _SP1 trying to compile postgis 1.5.3./configure went well but make gives me this error /usr/lib64/libxml2.a(parser.o): relocation R_X86_64_32S against `.rodata.str1.1' can not be used when making a shared object; recompile with -fPIC/usr/lib64/libxml2.a: could not read symbols: Bad valueDoes this mean I need to recompie libxml2? How should I deal with thisthanks for your helpSi vous n'êtes pas le destinataire de ce message, veuillez le détruire près avoir informé l'expéditeur de son erreur. Par ailleurs, il est interdit de copier ou de modifier ce courriel sans l'autorisation de l'auteur.L'Institut national de santé publique du Québec n'assume aucune responsabilité à l'égard du contenu des messages personnels envoyés par ses employés.___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] TIGER Geocoder and TIGER'sown Primary Keys and the use of tableA.statefp =tableB.statefp: I think its' a kludge
Steve, > > I don't see where the topology issue is relevant here. > Everything should be able to be done within SQL without > reliance upon topology, when TIGER's primary keys are used. > The only reason on my radar for wanting a normalized tiger dataset is for breaking edges at the local government level and to keep track of what edges were edited and so forth as well as fitting my existing geometries within the topology of TIGER. Your vision of normalization doesn't satisfy that need because a geometry is inherently a denormalized vision of a normalized topology. > Perhaps the geocoder as is has gone too far down the path > you've described to be easily adapted? > What benefit are you striving to get out of this aside? It would help if I understood that more clearly. I can forsee it would be faster if TLID and tfid were made primary keys, but I dismissed it for the time being since I didn't have time to benchmark the difference and also write routines to fix data of people who have already loaded their data. As far as making tlid a primary key. On further inspection of our loader, I realized we load into staging tables anyway so that we could rename all the fields that TIGER found the need to tack years on. So as part of that routine we drop the generated gid anyway, rename some others, add others, and drop others. So that probably wouldn't take too much effort. The easiest would be to use an EXCEPT clause since each county is loaded into staging and then added to the core state table. It would probably be slower than an append skip, but append skip is not an option since the data is already in the database by the time we get to that point. It won't change the queries though because we would still need the state joins to take advantage of constraint exclusion unless you have other thoughts on that. So that alone violates perfect normalization at the TLID level. Again keep in mind our priorities are: 1) Build a geocoder that is easy for everyone to load and use on any platform PostGIS runs on 2) User Maintainability - that means being able to drop state tables etc and load newer ones piecemeal or migrate a set of states to another database without having to change the underlying code. 3) Speed 4) Developer Maintainability -- I put this as last because it's too open to debate how you should structure your code so easy to some is not necessarily easy to others without knowing the developers invovled. For example I would never use a NATURAL JOIN because its too prone to all your code breaking if you happen to have two columns with same name but different meeting, but that's just me. Thanks, Regina http://www.postgis.us ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speeding up simple pt-in-poly lookups
Thanks everyone. Learned a lot. For now, I have decided to not go down the chop-and-reconstruct path. The following query does the job in 1.8 secs which is a pretty nice improvement over the previous 200+ seconds. SELECT Count(*) FROM collections c WHERE ST_Intersects( c.the_geom, ( SELECT ST_Buffer(ST_Simplify(n.the_geom, 0.75), 0.75) FROM base.continents n WHERE n.abbrev = 'NA' ) ); On Dec 21, 2011, at 11:38 AM, pcr...@pcreso.com wrote: > Puneet, > > Chopping polygons is pretty simple, with a grid & st_intersection(), but you > can certainly generalise polygons to reduce the number of vertices & size of > objects to de-toast... beware however that if you do this then you are > actually moving the polygon boundary, & therefore a point very near a > boundary may be inside the original country polygon but outside the > generalised/simplified one. > > You can address this by simplifying a buffer of the polygons, with the buffer > very slightly larger than the simplify distance, so that every simplified > version fully contains the original, but you will also have to check against > the original polygons to confirm the point is genuinely inside the original. > > As an alternative approach, you might also try selecting points where the > distance from a polygon is zero, as the ST_distance uses stabbing line > algorithm, and may be faster. The distance will be non-zero only for points > outside the polygon. > > Cheers, > > Brent Wood > > On 21/12/2011, at 12:06 PM, Mr. Puneet Kishor wrote: > >> >> On Dec 20, 2011, at 9:48 PM, Martin Davis wrote: >> >>> For more detail check out this thread on the same issue: >>> >>> http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html >> >> >> Thanks. Chopping up my coverage into hundreds of small regions is the last >> avenue I want to try. Going by the text of that email, it seems that "few, >> large, regions with many vertices (may be) the problem." I will try >> generalizing my continents so that I have "few, large regions with *very >> few* vertices" and see if that speeds up the SELECTs. >> >> >>> >>> On 12/20/2011 5:28 PM, Puneet Kishor wrote: On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote: > Chop up the continents into smaller pieces. > hmmm... I am not sure I understand the above. And then what? UNION each smaller piece query? > On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor > wrote: >> This is probably a really basic question... my ST_Within or >> ST_Intersects selecting points in a continent are way too slow (both >> take upward of 200 secs). >> >>SELECT Count(c_id) >>FROM c, continents n >>WHERE ST_Intersects(c.the_geom, n.the_geom) AND >>n.continent = 'North America'; >> >> >> Both tables have gist indexes on the geometries. The above query has the >> following plan >> >> "Aggregate (cost=9.66..9.67 rows=1 width=4)" >> " -> Nested Loop (cost=0.00..9.66 rows=1 width=4)" >> "Join Filter: _st_intersects(c.the_geom, n.the_geom)" >> "-> Seq Scan on continents n (cost=0.00..1.10 rows=1 >> width=32)" >> " Filter: ((continent)::text = 'North America'::text)" >> "-> Index Scan using pbdb__collections_the_geom on collections >> c (cost=0.00..8.30 rows=1 width=104)" >> " Index Cond: (c.the_geom&& n.the_geom)" >> >> The table c has approx 120K rows, and the continents table has 8 >> rows.Suggestions on how I can improve this? Yes, the computer is >> otherwise very swift and modern. >> >> >> >> -- >> Puneet Kishor ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] FW: [postgis-devel] PostGIS 2.0.0SVN: TIGER Geocoder and TIGER'sown Primary Keys and the use of tableA.statefp =tableB.statefp: I think its' a kludge
OK, I'll just see what I can achieve on my own and report back if I can make any progress. I don't believe that topology is any way a requirement, but do see where building some of the lookup tables a priori would eliminate point-in-poly queries on the fly. Thanks again, -Steve On Wed, 2011-12-21 at 14:37 -0500, Paragon Corporation wrote: > Just realized I sent this to the wrong list. > > -Original Message- > From: Paragon Corporation [mailto:l...@pcorp.us] > Sent: Wednesday, December 21, 2011 6:27 AM > To: 'PostGIS Development Discussion' > Subject: RE: [postgis-devel] PostGIS 2.0.0SVN: TIGER Geocoder and TIGER'sown > Primary Keys and the use of tableA.statefp =tableB.statefp: I think its' a > kludge > > > Yet my biggest personal > > challenge is reading through the sql code and the chained clauses, > > in > > for example 'geocode.sql'. A couple things that just made > > it tough for > > me to understand - beyond my limited ability to chain together all the > > clauses - is some of the uses of aliases in the sql code. > > At one point > > 'f' is an alias for 'featnames' at another point 'f' is an alias for > > 'faces.'I'm trying to re-write stuff by dropping the > > aliases in > > favor of explicit table.attribute syntax so I can more > > > explicitly see > > the actual tables and attributes with which I'm working. > > Steve, > > I have mixed feelings about this. I wasn't the one that wrote the bulk of > the sql. > > Steve Frost and his friend (who I can't quite remember his name) did that > and I didn't have any problems reading there code. They did a great job in > fact. > > The main issue I have with using the correct name is 2 > > 1) Practicality - you can't because some pieces do require joining by the > same table more than once so at least one is not going to have the same name > and the other is not. Sure you could alias it to be suffixed with faceA , > faceB etc. So that's minor > 2) It's a pain to type and the extended length makes it harder for me to > read as I have more lines to scan to gain clarity. > > That said I'm not totally against the idea if people feel it would make it > easier for them to understand and contribute. > > On the pro side, I think I did get a bit lazy and when I was folding queries > or expanding them it was faster to just move the logic up and change the > alias to where it was before. Thus getting into your frustration with the > same alias used to mean different things. > > That should probably be changed. > > > > I do think I have a 100% complete and properly normalized TIGER 2010 > > database to work against if that helps. > > It would be useful to see if that helps with the speed of some things. I > think the faces it would. edges I doubt it. > > As far as removing statefp -- I don't see that working without just dumping > all the data into the same tables. Without that your vision of a perfectly > normalized TIGER is not attainable as you'd still have dupes in the state > tables. > > > Keep in mind we chose to break data out by state not just for speed reasons, > but for maintainability. > > a) We work with a lot of of users and most of them need only one state full > of data. > Its easier to load one set of state tables from one database to another than > to try to copy the whole thing for obvious reasons. > > That is actually the primary reason we broke the data up this way. > > b) The second is for speed, since for at least lower systems with lower > memory the partitioned approach seemed to be better speedwise. > For a system with lots of memory may not make too much of a difference. > > I can not stress enough that we care more about ease of use and speed than > how normalized our data is. Normalization is important when you are > managing / modifying TIGER data, but not when you are using it for > geocoding. With that said, I would suggest you look at the PostGIS Topology > model. > > If ever we were to build a geocoder on a normalized system (which would have > the side benefity of being useful for local gov TIGER management), it would > be based on PostGIS Topology. > > Thanks, > Regina > http://www.postgis.us > > > > -- Steve Walker Middle Fork Geographic Information Services (360)671-2505 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] raster output
Ahhh, you'll be looking for a while. :) PostGIS raster does not currently interpret raster band data as color information. If it started out as RGB, you can input all three bands and manipulate them however you see fit. However, you can't output a color jpeg/png if you're starting with a single band of data. Additionally, if you import a single band image with associated pallete, you'll lose the color information. On the upside, if you have a full color image, you can read it in manipulate it, and spit it back out. Try exporting the single band of interest, then adding a pallete using the external program of your choice. That will also let you "stretch" the image to your heart's content. I'm 90% sure you can do all your desired postprocessing with ImageMagick. :) Bryce -Original Message- From: postgis-users-boun...@postgis.refractions.net [mailto:postgis-users-boun...@postgis.refractions.net] On Behalf Of Stephen Crawford Sent: Wednesday, December 21, 2011 3:51 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] raster output Thanks for getting back to me. I want to do something like #3, but I just don't understand the work flow. I have raw data as a one band raster in postGIS, and the (seemingly) simple thing i would like to do is output the entire table as a jpg or png with the cells classified by color. I see references to "color tables" and "palettes". I can't seem to figure out where I can define "if cell val < 0.5, color = 0, 0, 255" etc. ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] FW: [postgis-devel] PostGIS 2.0.0SVN: TIGER Geocoder and TIGER'sown Primary Keys and the use of tableA.statefp =tableB.statefp: I think its' a kludge
Just realized I sent this to the wrong list. -Original Message- From: Paragon Corporation [mailto:l...@pcorp.us] Sent: Wednesday, December 21, 2011 6:27 AM To: 'PostGIS Development Discussion' Subject: RE: [postgis-devel] PostGIS 2.0.0SVN: TIGER Geocoder and TIGER'sown Primary Keys and the use of tableA.statefp =tableB.statefp: I think its' a kludge > Yet my biggest personal > challenge is reading through the sql code and the chained clauses, > in > for example 'geocode.sql'. A couple things that just made > it tough for > me to understand - beyond my limited ability to chain together all the > clauses - is some of the uses of aliases in the sql code. > At one point > 'f' is an alias for 'featnames' at another point 'f' is an alias for > 'faces.'I'm trying to re-write stuff by dropping the > aliases in > favor of explicit table.attribute syntax so I can more > > explicitly see > the actual tables and attributes with which I'm working. Steve, I have mixed feelings about this. I wasn't the one that wrote the bulk of the sql. Steve Frost and his friend (who I can't quite remember his name) did that and I didn't have any problems reading there code. They did a great job in fact. The main issue I have with using the correct name is 2 1) Practicality - you can't because some pieces do require joining by the same table more than once so at least one is not going to have the same name and the other is not. Sure you could alias it to be suffixed with faceA , faceB etc. So that's minor 2) It's a pain to type and the extended length makes it harder for me to read as I have more lines to scan to gain clarity. That said I'm not totally against the idea if people feel it would make it easier for them to understand and contribute. On the pro side, I think I did get a bit lazy and when I was folding queries or expanding them it was faster to just move the logic up and change the alias to where it was before. Thus getting into your frustration with the same alias used to mean different things. That should probably be changed. > I do think I have a 100% complete and properly normalized TIGER 2010 > database to work against if that helps. It would be useful to see if that helps with the speed of some things. I think the faces it would. edges I doubt it. As far as removing statefp -- I don't see that working without just dumping all the data into the same tables. Without that your vision of a perfectly normalized TIGER is not attainable as you'd still have dupes in the state tables. Keep in mind we chose to break data out by state not just for speed reasons, but for maintainability. a) We work with a lot of of users and most of them need only one state full of data. Its easier to load one set of state tables from one database to another than to try to copy the whole thing for obvious reasons. That is actually the primary reason we broke the data up this way. b) The second is for speed, since for at least lower systems with lower memory the partitioned approach seemed to be better speedwise. For a system with lots of memory may not make too much of a difference. I can not stress enough that we care more about ease of use and speed than how normalized our data is. Normalization is important when you are managing / modifying TIGER data, but not when you are using it for geocoding. With that said, I would suggest you look at the PostGIS Topology model. If ever we were to build a geocoder on a normalized system (which would have the side benefity of being useful for local gov TIGER management), it would be based on PostGIS Topology. Thanks, Regina http://www.postgis.us ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] TIP: Loading TIGER data with Primary Keys
Steve, You might be right, but I would validate this because in the past, edges on the county and state boundary only had the side that was in the county populated and the documentation called out the fact that you could detect the boundary segments based on this fact. I just did a quick look through the current documentation and could not find a reference to this so it might be old information. -Steve On 12/21/2011 11:31 AM, Steve Walker wrote: Steve, I think, actually, perhaps, no. The EDGES feature and its attributes should be the same in both county shapefiles. The ADDR record is where the feature's address attributes reside, including the 'side' attribute. tlid| numeric(10,0) | fromhn | character(12) | tohn| character(12) | side| character(1) | zip | character(5) | plus4 | character(4) | fromtyp | character(1) | totyp | character(1) | arid| character(22) | not null mtfcc | character(5) | A single line segment, with a unique TLID, may be represented in multiple linear features, represented by LINEARID, which may then be linked to mutiple address records, via the ARID primary key of the ADDR table. So, CountyA and CountyB will share the same EDGE, but their ADDR records will differ as you noted. But since ADDR has its own primary key in ARID, the failure of the line feature in the CountyB_edges shapefile will not affect loading of the ADDR record, since it will have a differing ARID primary key value than the first. - On Wed, 2011-12-21 at 00:36 -0500, Stephen Woodbridge wrote: Hi Steve, This is a nice tip but it does have a problem. In the tiger data "County Line Rd" will only be populated on the right OR left side that is in the county dataset it belongs to. So if you load CountyA first then you will get one side of the data right or left. And when you get the primary key failure on the next county that has that TLID, it will get skipped and you will loss the other side attributes. :( You might be able to write a plpgsql trigger that can catch the primary key violation and update the existing record, but I haven't tried to do that. -Steve W On 12/20/2011 10:33 PM, Steve Walker wrote: Hi, I want to offer the following tip on how to properly build and load a nation-wide (or subset thereof) US Census Bureau TIGER database into PostGIS using ogr2ogr, in such a manner as to maintain the primary key integrity inherent in the native TIGER database. For the purpose of this example, I'll identify two adjacent counties within the same state: CountyA and CountyB, and we will work only with the edges theme. We can imagine that a road named "County Line Rd" forms part of the boundary between these two counties. In the TIGER database, the road will represented by one or more EDGEs. We'll simplify this to assume that there is exactly one edge feature representing "County Line Rd" and that the edge feature has a unique Tiger Line Identifier (TLID) of 12345687890 which is unique throughout the entire TIGER database, nationwide. (We'll also simplify this to ignore N-to-N relations between edges, roads, and names etc. They're important, but irrelevant to this discussion.) So far so good. Unfortunately however, the countyA_edges.shp and countyB_edges.shp each maintain a representation of this feature. TLID 1234567890, although a unique primary key, is duplicated between two shapefiles. Normally, ogr2ogr would neither notice nor would it care. This is because ogr2ogr will by default add and populate an arbitrary primary key attribute 'ogc_fid' when loading data. Thus, the plain vanilla ogr2ogr load of TIGER will not fail due to any duplicate primary key violations, but at the expense of losing the normalized integrity of the database. We will end up with County Line Rd and TLID 1234567890 being duplicated in the database, and we won't be able to leverage the native TLID primary key in all our subsequent operations. Here is how to fix that: So the first thing we would reasonably do would be something like this: ALTER TABLE edges DROP COLUMN ogc_fid; This would dump the arbitrary primary key set up by ogr2ogr And then we would attempt: ALTER TABLE edges ADD PRIMARY KEY (tlid); Which would fail once it hit the second record with a TLID of 1234567890 (ie because it duplicates the primary key constraint.) So, this cannot be easily done after the fact: IE when we have a whole state or the whole nation already loaded into the database. --- OK, so here is the TIP (pseudo-code): Starting with an totally empty database: shell: #ogr2ogr countyA_edges.shp EDGES load the first shapefile. psql: psql: ALTER TABLE edges DROP column ogc_fid ### drop the arbitrary primary key psql: ALTER TABLE edges ADD PRIMARY KEY (tlid); ### add the real primary key. (back to the shell:) #ogr2ogr -skipfailures -gt 1 countyB_edges.shp EDGES #ogr2ogr -skipfailures -gt 1 countyC_edges.shp EDGES #ogr2ogr -skipfailure
Re: [postgis-users] speeding up simple pt-in-poly lookups
Puneet, Chopping polygons is pretty simple, with a grid & st_intersection(), but you can certainly generalise polygons to reduce the number of vertices & size of objects to de-toast... beware however that if you do this then you are actually moving the polygon boundary, & therefore a point very near a boundary may be inside the original country polygon but outside the generalised/simplified one. You can address this by simplifying a buffer of the polygons, with the buffer very slightly larger than the simplify distance, so that every simplified version fully contains the original, but you will also have to check against the original polygons to confirm the point is genuinely inside the original. As an alternative approach, you might also try selecting points where the distance from a polygon is zero, as the ST_distance uses stabbing line algorithm, and may be faster. The distance will be non-zero only for points outside the polygon. Cheers, Brent Wood On 21/12/2011, at 12:06 PM, Mr. Puneet Kishor wrote: > > On Dec 20, 2011, at 9:48 PM, Martin Davis wrote: > >> For more detail check out this thread on the same issue: >> >> http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html > > > Thanks. Chopping up my coverage into hundreds of small regions is the last > avenue I want to try. Going by the text of that email, it seems that "few, > large, regions with many vertices (may be) the problem." I will try > generalizing my continents so that I have "few, large regions with *very few* > vertices" and see if that speeds up the SELECTs. > > >> >> On 12/20/2011 5:28 PM, Puneet Kishor wrote: >>> On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote: >>> Chop up the continents into smaller pieces. >>> >>> hmmm... I am not sure I understand the above. And then what? UNION each >>> smaller piece query? >>> >>> On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor wrote: > This is probably a really basic question... my ST_Within or ST_Intersects > selecting points in a continent are way too slow (both take upward of 200 > secs). > > SELECT Count(c_id) > FROM c, continents n > WHERE ST_Intersects(c.the_geom, n.the_geom) AND > n.continent = 'North America'; > > > Both tables have gist indexes on the geometries. The above query has the > following plan > > "Aggregate (cost=9.66..9.67 rows=1 width=4)" > " -> Nested Loop (cost=0.00..9.66 rows=1 width=4)" > " Join Filter: _st_intersects(c.the_geom, n.the_geom)" > " -> Seq Scan on continents n (cost=0.00..1.10 rows=1 width=32)" > " Filter: ((continent)::text = 'North America'::text)" > " -> Index Scan using pbdb__collections_the_geom on collections > c (cost=0.00..8.30 rows=1 width=104)" > " Index Cond: (c.the_geom&& n.the_geom)" > > The table c has approx 120K rows, and the continents table has 8 > rows.Suggestions on how I can improve this? Yes, the computer is > otherwise very swift and modern. > > > > -- > Puneet Kishor >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >>> - >>> No virus found in this message. >>> Checked by AVG - www.avg.com >>> Version: 2012.0.1890 / Virus Database: 2109/4692 - Release Date: 12/20/11 >>> >>> >> ___ >> 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 mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] TIP: Loading TIGER data with Primary Keys
Steve, I think, actually, perhaps, no. The EDGES feature and its attributes should be the same in both county shapefiles. The ADDR record is where the feature's address attributes reside, including the 'side' attribute. tlid| numeric(10,0) | fromhn | character(12) | tohn| character(12) | side| character(1) | zip | character(5) | plus4 | character(4) | fromtyp | character(1) | totyp | character(1) | arid| character(22) | not null mtfcc | character(5) | A single line segment, with a unique TLID, may be represented in multiple linear features, represented by LINEARID, which may then be linked to mutiple address records, via the ARID primary key of the ADDR table. So, CountyA and CountyB will share the same EDGE, but their ADDR records will differ as you noted. But since ADDR has its own primary key in ARID, the failure of the line feature in the CountyB_edges shapefile will not affect loading of the ADDR record, since it will have a differing ARID primary key value than the first. - On Wed, 2011-12-21 at 00:36 -0500, Stephen Woodbridge wrote: > Hi Steve, > > This is a nice tip but it does have a problem. In the tiger data "County > Line Rd" will only be populated on the right OR left side that is in the > county dataset it belongs to. So if you load CountyA first then you will > get one side of the data right or left. And when you get the primary key > failure on the next county that has that TLID, it will get skipped and > you will loss the other side attributes. :( > > You might be able to write a plpgsql trigger that can catch the primary > key violation and update the existing record, but I haven't tried to do > that. > > -Steve W > > On 12/20/2011 10:33 PM, Steve Walker wrote: > > Hi, > > > > I want to offer the following tip on how to properly build and load a > > nation-wide (or subset thereof) US Census Bureau TIGER database into > > PostGIS using ogr2ogr, in such a manner as to maintain the primary key > > integrity inherent in the native TIGER database. > > > > For the purpose of this example, I'll identify two adjacent counties > > within the same state: CountyA and CountyB, and we will work only with > > the edges theme. > > > > We can imagine that a road named "County Line Rd" forms part of the > > boundary between these two counties. > > > > In the TIGER database, the road will represented by one or more EDGEs. > > We'll simplify this to assume that there is exactly one edge feature > > representing "County Line Rd" and that the edge feature has a unique > > Tiger Line Identifier (TLID) of 12345687890 which is unique throughout > > the entire TIGER database, nationwide. (We'll also simplify this to > > ignore N-to-N relations between edges, roads, and names etc. They're > > important, but irrelevant to this discussion.) > > > > So far so good. Unfortunately however, the countyA_edges.shp and > > countyB_edges.shp each maintain a representation of this feature. TLID > > 1234567890, although a unique primary key, is duplicated between two > > shapefiles. > > > > Normally, ogr2ogr would neither notice nor would it care. This is > > because ogr2ogr will by default add and populate an arbitrary primary > > key attribute 'ogc_fid' when loading data. > > > > Thus, the plain vanilla ogr2ogr load of TIGER will not fail due to any > > duplicate primary key violations, but at the expense of losing the > > normalized integrity of the database. We will end up with County Line > > Rd and TLID 1234567890 being duplicated in the database, and we won't be > > able to leverage the native TLID primary key in all our subsequent > > operations. > > > > Here is how to fix that: > > > > > > So the first thing we would reasonably do would be something like this: > > > > ALTER TABLE edges DROP COLUMN ogc_fid; > > > > This would dump the arbitrary primary key set up by ogr2ogr > > > > And then we would attempt: > > > > ALTER TABLE edges ADD PRIMARY KEY (tlid); > > > > Which would fail once it hit the second record with a TLID of 1234567890 > > (ie because it duplicates the primary key constraint.) > > > > So, this cannot be easily done after the fact: IE when we have a whole > > state or the whole nation already loaded into the database. > > --- > > > > > > OK, so here is the TIP (pseudo-code): > > > > Starting with an totally empty database: > > > > > > shell: > > #ogr2ogr countyA_edges.shp EDGES load the first shapefile. > > > > psql: > > psql: ALTER TABLE edges DROP column ogc_fid ### drop the arbitrary > > primary key > > > > psql: ALTER TABLE edges ADD PRIMARY KEY (tlid); ### add the real > > primary key. > > > > (back to the shell:) > > > > #ogr2ogr -skipfailures -gt 1 countyB_edges.shp EDGES > > #ogr2ogr -skipfailures -gt 1 countyC_edges.shp EDGES > > #ogr2ogr -skipfailures -gt 1 countyD_edges.shp EDGES > > #ogr2ogr -skipfailures -gt 1 countyE_edges.shp EDGES > > #ogr2ogr -skipfailures -gt 1 countyF
Re: [postgis-users] raster output
Thanks for getting back to me. I want to do something like #3, but I just don't understand the work flow. I have raw data as a one band raster in postGIS, and the (seemingly) simple thing i would like to do is output the entire table as a jpg or png with the cells classified by color. I see references to "color tables" and "palettes". I can't seem to figure out where I can define "if cell val < 0.5, color = 0, 0, 255" etc. Thanks, Steve On 12/20/2011 6:13 PM, Paragon Corporation wrote: 3) You can use gdal_translate (compiled with PostgreSQL support) to output a postgis raster query. -- Stephen Crawford Center for Environmental Informatics The Pennsylvania State University ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] upgrading Guide with problem
Hello, I have a lot of problems with ch.2.7.1 at http://www.postgis.org/docs/ch02.html#upgrading -- 1) postgis_upgrade_13_to_15.sql NOT EXIST The links not have any file. locate postgis_upgrade_13_to_15.sql not exist for downlaod ONLY with google, searching with "filetype:sql postgis_upgrade_13_to_15.sql" we have only one download, http://mapserver.sara.nl/scratch/pgsql843/share/contrib/postgis-1.5/postgis_upgrade_13_to_15.sql PS: http://svn.osgeo.org/postgis/trunk/postgis/ not have the file. 2) problem with the ch.2.7.1 recomendation, $ psql -f postgis_upgrade_13_to_15.sql -d your_spatial_database psql: FATAL: role "root" not exist 3) try to create only a important function, CREATE OR REPLACE FUNCTION _ST_AsGeoJson(int4, geometry, int4, int4) RETURNS TEXT AS '$libdir/postgis-1.5','LWGEOM_asGeoJson' LANGUAGE 'C' IMMUTABLE STRICT; -- file "$libdir/postgis-1.5" NOT EXIST 4) ... CLUES, select postgis_lib_version(); 1.3.3 My installation have things like /usr/share/doc/postgresql-8.3-postgis and /usr/share/java/postgis.jar ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Dropping the PostGIS Java components
On Wed, Dec 21, 2011 at 01:13:50PM +0100, Maria Arias de Reyna wrote: > El Miércoles, 21 de Diciembre de 2011, Sandro Santilli escribió: > I don't know about the ejb* and pljava directories, but the jdbc driver > should > stay there as it should be the "default" way to access a postGIS database > from > Java. For example, the HIbernate Spatial extension uses this driver[1]. > > Are you sure there is no-one maintaining it? Yesterday there was a commit[2]. It was mine, but couldn't test it. Can you ? > Anyway, if you need someone to maintain it, I can take a look as I am > interested on the continuity of this driver. Yes Maria, we do need someone to maintain it and you're very welcome. If you're not subscribed to the development mailing list please do so and state your interest for the role. Thank you ! --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Dropping the PostGIS Java components
El Miércoles, 21 de Diciembre de 2011, Sandro Santilli escribió: > On Wed, Dec 21, 2011 at 11:32:03AM +0100, Maria Arias de Reyna wrote: > > El Miércoles, 21 de Diciembre de 2011, Sandro Santilli escribió: > > > Is there anyone using the Java components of PostGIS ? > > > We're wondering if we should just drop them as they > > > are not really maintained by anyone. > > > Maybe every Javaist is using some other components ? > > > > What are exactly the Java components of PostGIS? The java jdbc driver? > > The java/ subdir of PostGIS source tree: > http://trac.osgeo.org/postgis/browser/trunk/java > > There is a jdbc subdir in there but I dont know more than that. > I don't know about the ejb* and pljava directories, but the jdbc driver should stay there as it should be the "default" way to access a postGIS database from Java. For example, the HIbernate Spatial extension uses this driver[1]. Are you sure there is no-one maintaining it? Yesterday there was a commit[2]. Anyway, if you need someone to maintain it, I can take a look as I am interested on the continuity of this driver. [1]http://www.hibernatespatial.org/ [2]http://trac.osgeo.org/postgis/changeset/8481/trunk/java/jdbc -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Dropping the PostGIS Java components
On Wed, Dec 21, 2011 at 11:32:03AM +0100, Maria Arias de Reyna wrote: > El Miércoles, 21 de Diciembre de 2011, Sandro Santilli escribió: > > Is there anyone using the Java components of PostGIS ? > > We're wondering if we should just drop them as they > > are not really maintained by anyone. > > Maybe every Javaist is using some other components ? > > What are exactly the Java components of PostGIS? The java jdbc driver? The java/ subdir of PostGIS source tree: http://trac.osgeo.org/postgis/browser/trunk/java There is a jdbc subdir in there but I dont know more than that. --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] BYTE_ORDER in SVN trunk on Solaris/i386
Sandro Santilli wrote: > We used to have our owh byte order detector as well. May still be > there (symbol getMachineEndian). Should do fine. Oh, I just found out there _is_ a ticket at: http://trac.osgeo.org/postgis/ticket/1172 which I didn't notice at first. Cheers, Martin. -- Unix _IS_ user friendly - it's just selective about who its friends are ! -- ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Dropping the PostGIS Java components
El Miércoles, 21 de Diciembre de 2011, Sandro Santilli escribió: > Is there anyone using the Java components of PostGIS ? > We're wondering if we should just drop them as they > are not really maintained by anyone. > Maybe every Javaist is using some other components ? What are exactly the Java components of PostGIS? The java jdbc driver? > > > --strk; > > ,--o-. > > | __/ |Thank you for PostGIS-2.0 Topology ! > | > | / 2.0 |http://www.pledgebank.com/postgistopology > > `-o--' > > ___ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users -- María Arias de Reyna Domínguez Área de Operaciones Emergya Consultoría Tfno: +34 954 51 75 77 / +34 607 43 74 27 Fax: +34 954 51 64 73 www.emergya.com ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Dropping the PostGIS Java components
Is there anyone using the Java components of PostGIS ? We're wondering if we should just drop them as they are not really maintained by anyone. Maybe every Javaist is using some other components ? --strk; ,--o-. | __/ |Thank you for PostGIS-2.0 Topology ! | / 2.0 |http://www.pledgebank.com/postgistopology `-o--' ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] speeding up simple pt-in-poly lookups
Puneet, Trying to find which country a large set of points was in we have actually found it much quicker to find points in provinces (smaller polygons) and then return the country code associated with the province. No chopping anything, so I would guess you could use a world map, allocate a continent to each country in the world map and query it. The speed up we saw was (I guess) for the same reason - the bbox was efficient. cheers Ben On 21/12/2011, at 12:06 PM, Mr. Puneet Kishor wrote: > > On Dec 20, 2011, at 9:48 PM, Martin Davis wrote: > >> For more detail check out this thread on the same issue: >> >> http://postgis.refractions.net/pipermail/postgis-users/2011-November/031345.html > > > Thanks. Chopping up my coverage into hundreds of small regions is the last > avenue I want to try. Going by the text of that email, it seems that "few, > large, regions with many vertices (may be) the problem." I will try > generalizing my continents so that I have "few, large regions with *very few* > vertices" and see if that speeds up the SELECTs. > > >> >> On 12/20/2011 5:28 PM, Puneet Kishor wrote: >>> On Dec 20, 2011, at 7:21 PM, Paul Ramsey wrote: >>> Chop up the continents into smaller pieces. >>> >>> hmmm... I am not sure I understand the above. And then what? UNION each >>> smaller piece query? >>> >>> On Tue, Dec 20, 2011 at 4:35 PM, Puneet Kishor wrote: > This is probably a really basic question... my ST_Within or ST_Intersects > selecting points in a continent are way too slow (both take upward of 200 > secs). > > SELECT Count(c_id) > FROM c, continents n > WHERE ST_Intersects(c.the_geom, n.the_geom) AND > n.continent = 'North America'; > > > Both tables have gist indexes on the geometries. The above query has the > following plan > > "Aggregate (cost=9.66..9.67 rows=1 width=4)" > " -> Nested Loop (cost=0.00..9.66 rows=1 width=4)" > "Join Filter: _st_intersects(c.the_geom, n.the_geom)" > "-> Seq Scan on continents n (cost=0.00..1.10 rows=1 width=32)" > " Filter: ((continent)::text = 'North America'::text)" > "-> Index Scan using pbdb__collections_the_geom on collections > c (cost=0.00..8.30 rows=1 width=104)" > " Index Cond: (c.the_geom&& n.the_geom)" > > The table c has approx 120K rows, and the continents table has 8 > rows.Suggestions on how I can improve this? Yes, the computer is > otherwise very swift and modern. > > > > -- > Puneet Kishor >>> ___ >>> postgis-users mailing list >>> postgis-users@postgis.refractions.net >>> http://postgis.refractions.net/mailman/listinfo/postgis-users >>> >>> >>> - >>> No virus found in this message. >>> Checked by AVG - www.avg.com >>> Version: 2012.0.1890 / Virus Database: 2109/4692 - Release Date: 12/20/11 >>> >>> >> ___ >> 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
