[postgis-users] Re: Zip code polygons (dnrg)
Mark Thomas
spatialguru.net at gmail.com
Mon Jul 17 11:00:35 PDT 2006
Dana,
As the robot veg said, the only free polygon shapefiles that I know about
that are close to what you want is the ZCTA polygon shapefiles from the US
Census bureaus. These are what I'm actually using, ZCTAs, since my app is
based on demographic data from the census bureau and other sources. You are
accurate that the USPS does not provide these either. If you have a license
for ArcIMS or ArcGIS you might be able to use the zip code polygons in the
ESRI data & maps CD/DVD set, but that of course depends on what you'll use
them for since ESRI's licensing is so strict. I wish I could tell you how
to make one, but I've never tried. If you find a good source let us all
know! The good new is that once you have decent data the PL/PGSQL is
simple. If you run into trouble just shoot me an email or post on the list.
Best of luck,
Mark
On 7/14/06, postgis-users-request at postgis.refractions.net <
postgis-users-request at postgis.refractions.net> wrote:
>
> Send postgis-users mailing list submissions to
> postgis-users at postgis.refractions.net
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> or, via email, send a message with subject or body 'help' to
> postgis-users-request at postgis.refractions.net
>
> You can reach the person managing the list at
> postgis-users-owner at postgis.refractions.net
>
> When replying, please edit your Subject line so it is more specific
> than "Re: Contents of postgis-users digest..."
>
>
> Today's Topics:
>
> 1. Zip code polygons (dnrg)
> 2. Re: Who is Using PostGIS? For What? (Hern?n De Angelis)
> 3. Re: Who is Using PostGIS? For What? (Paul Ramsey)
> 4. Re: Zip code polygons (the Robot Vegetable)
> 5. Re: Who is Using PostGIS? For What? (Hern?n De Angelis)
> 6. Re: Who is Using PostGIS? For What? (Jessica M Salmon)
> 7. Re: Who is Using PostGIS? For What? (Stephen Davies)
> 8. optimize: count up points near a point (Josh Livni)
> 9. Re: optimize: count up points near a point (Michael Fuhr)
> 10. Re: optimize: count up points near a point (Josh Livni)
> 11. Re: optimize: count up points near a point (Stephen Woodbridge)
> 12. Re: postgis-users Digest, Vol 45, Issue 13
> (Bernhard Reimar Hoefle)
> 13. ERROR: Operation on two GEOMETRIES with different SRIDs (Johan V)
> 14. RE: ERROR: Operation on two GEOMETRIES with differentSRIDs
> (Mark Cave-Ayland)
> 15. Re: ERROR: Operation on two GEOMETRIES with differentSRIDs
> (Johan V)
> 16. RE: ERROR: Operation on two GEOMETRIES withdifferentSRIDs
> (Mark Cave-Ayland)
> 17. Re: ERROR: Operation on two GEOMETRIES withdifferentSRIDs
> (Johan V)
> 18. RE: ERROR: Operation on two GEOMETRIESwithdifferentSRIDs
> (Mark Cave-Ayland)
> 19. Re: ERROR: Operation on two GEOMETRIESwithdifferentSRIDs
> (Johan V)
> 20. Re: ERROR: Operation on two GEOMETRIES withdifferentSRIDs
> (Ezequias Rodrigues da Rocha)
> 21. RE: Who is Using PostGIS? For What? (Robert Burgholzer)
> 22. Re: Who is Using PostGIS? For What? (Markus Schaber)
> 23. RE: ERROR: Operation on twoGEOMETRIESwithdifferentSRIDs
> (Mark Cave-Ayland)
> 24. RE: ERROR: Operation on twoGEOMETRIES withdifferentSRIDs
> (Mark Cave-Ayland)
> 25. Re: Who is Using PostGIS? For What? (Eduardo Lu?s Garcia Escovar)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 13 Jul 2006 14:24:27 -0700 (PDT)
> From: dnrg <dananrg at yahoo.com>
> Subject: [postgis-users] Zip code polygons
> To: postgis-users at postgis.refractions.net
> Message-ID: <20060713212427.26035.qmail at web33103.mail.mud.yahoo.com>
> Content-Type: text/plain; charset=iso-8859-1
>
> > Mark Thomas - independent GIS developer/contractor
> > 1) geocoding, zip code lookups, radius lookups (i.e.
> > is gimme everything within XXX miles of 123 main st,
> > somewhere, AL 12345), and
>
> Hi Mark and list. I need to do the same with zip codes
> and radius lookups.
>
> Where can I get a shapefile of zip code polygons to
> load into PostGIS? Is anything like this available for
> free, or must I purchase it from someone? Free is
> better. I don't believe the USPS maintains a zip code
> polygon shapefile. How does one go about getting or
> creating one?
>
> Thanks.
>
> Dana
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
>
> ------------------------------
>
> Message: 2
> Date: Thu, 13 Jul 2006 23:41:33 +0200
> From: Hern?n De Angelis <hernan.deangelis at glocalnet.net>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <200607132341.34584.hernan.deangelis at glocalnet.net>
> Content-Type: text/plain; charset="iso-8859-1"
>
> > Who are you?
> Hernán De Angelis, Department of Physical Geography and Quaternary
> Geology,
> Stockholm University, Sweden
>
> > What are you using PostGIS for?
> Storage and analysis of glaciological and palaeoglaciological features.
>
> > Why did you choose PostGIS?
> Because it is powerful, reliable, flexible, stable and free.
>
> > What makes you happy about your PostGIS installation?
> I can access databases from several free GIS applications, perl scripts
> and
> the command line.
>
> > What makes you unhappy about your PostGIS installation?
> Nothing about PostGIS in itself, but has to do with it anyway: there are
> still
> few free GIS applications providing a fully implemented on-screen
> digitizing
> environment for PostGIS.
>
> In my particular case, for example, I usually digitize features that I
> interpret on satellite imagery. Therefore, I need a GIS application that
> is
> able to display images, with the posibility of applying enhancements, and
> at
> the same time allows to directly digitize in PostGIS. As far as I know the
> only free application that is able to do that is gvSIG, although it is
> still
> in an early stage of development. uDig is good for digitizing but it
> doesn't
> display big satellite images. Another good alternative, QGIS, has very
> basic
> digitizing tools and displays images, but it is unable of applying
> enhancements (on grey scales). So, until now I am forced to digitize in
> shapefile format or GRASS and then exporting to PostGIS. I will be glad
> when
> an application like gvSIG or similar reaches a more advanced stage where
> it
> is possible to do production work.
>
> >
> > Those who want to remain in confidence, but still want to make their
> > feelings known, please respond to me directly.
> >
> > I am going to be writing up a handful of PostGIS users into full
> > white-papers for the web site and my own conference talks, so please, if
> > you think you have a compelling PostGIS story, start by giving us a
> > summary here on the list!
> >
> > I love hearing these stories!
> >
> > Paul
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> ------------------------------
>
> Message: 3
> Date: Thu, 13 Jul 2006 14:45:10 -0700
> From: Paul Ramsey <pramsey at refractions.net>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B6BEE6.7030305 at refractions.net>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Two uDig comments:
>
> 1 - In the interim, if you publish your satimage using a WMS service
> like Mapserver you can bring WMS impages into uDig without any problems
> and digitize off of them. (Also, try the v. v. latest uDig 1.1RC
> candidates, as the PostGIS support has been worked on a lot recently).
> 2 - There is a big upgrade in the raster/image handling in geotools
> going through right now, flowing out of work done in NATO over the past
> year. When that work is in, uDig gets a free and good upgrade in imagery
> support, which will take very little effort to bring forward to the
> user. Hopefully before the 1.1 release, but perhaps for 1.1.1.
>
> Paul
>
> Hernán De Angelis wrote:
>
> > In my particular case, for example, I usually digitize features that I
> > interpret on satellite imagery.
> ...
> > uDig is good for digitizing but it doesn't
> > display big satellite images.
>
>
> ------------------------------
>
> Message: 4
> Date: Thu, 13 Jul 2006 14:45:00 -0700 (PDT)
> From: the Robot Vegetable <veg at dvandva.org>
> Subject: Re: [postgis-users] Zip code polygons
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID:
> <Pine.LNX.4.58.0607131440280.15556 at hosting.midvalleyhosting.com>
> Content-Type: TEXT/PLAIN; charset=US-ASCII
>
>
> Be careful with ZIP codes, they really aren't "areas" per se.
>
> That said, this discusses abit:
>
> http://www.census.gov/geo/ZCTA/zcta.html
>
> and files are accessible through this page:
>
> http://www.census.gov/geo/www/bob/bdy_files.html
>
>
> veg
>
>
>
> On Thu, 13 Jul 2006, dnrg wrote:
>
> > > Mark Thomas - independent GIS developer/contractor
> > > 1) geocoding, zip code lookups, radius lookups (i.e.
> > > is gimme everything within XXX miles of 123 main st,
> > > somewhere, AL 12345), and
> >
> > Hi Mark and list. I need to do the same with zip codes
> > and radius lookups.
> >
> > Where can I get a shapefile of zip code polygons to
> > load into PostGIS? Is anything like this available for
> > free, or must I purchase it from someone? Free is
> > better. I don't believe the USPS maintains a zip code
> > polygon shapefile. How does one go about getting or
> > creating one?
> >
> > Thanks.
> >
> > Dana
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam protection around
> > http://mail.yahoo.com
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 5
> Date: Thu, 13 Jul 2006 23:59:02 +0200
> From: Hern?n De Angelis <hernan.deangelis at glocalnet.net>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: postgis-users at postgis.refractions.net
> Message-ID: <200607132359.04162.hernan.deangelis at glocalnet.net>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Paul,
>
> Thanks for the comments. I will try your trick and will be looking at the
> latest uDig.
>
> BTW, I forgot to mention, as another advantage, the active and friendly
> PostGIS community ;-)
>
> Hernán
>
>
> El Jueves 13 Julio 2006 23.45, Paul Ramsey escribió:
> > Two uDig comments:
> >
> > 1 - In the interim, if you publish your satimage using a WMS service
> > like Mapserver you can bring WMS impages into uDig without any problems
> > and digitize off of them. (Also, try the v. v. latest uDig 1.1RC
> > candidates, as the PostGIS support has been worked on a lot recently).
> > 2 - There is a big upgrade in the raster/image handling in geotools
> > going through right now, flowing out of work done in NATO over the past
> > year. When that work is in, uDig gets a free and good upgrade in imagery
> > support, which will take very little effort to bring forward to the
> > user. Hopefully before the 1.1 release, but perhaps for 1.1.1.
> >
> > Paul
> >
> > Hernán De Angelis wrote:
> > > In my particular case, for example, I usually digitize features that I
> > > interpret on satellite imagery.
> >
> > ...
> >
> > > uDig is good for digitizing but it doesn't
> > > display big satellite images.
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> ------------------------------
>
> Message: 6
> Date: Thu, 13 Jul 2006 16:17:53 -0600
> From: Jessica M Salmon <jmsalmon at fs.fed.us>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID:
> <
> OF0949CCEB.2ADAD320-ON872571AA.007741A4-872571AA.007A7CDE at fs.fed.us>
> Content-Type: text/plain; charset=US-ASCII
>
> > Who are you?
> >From the top, down: We are the United States Department of Agriculture,
> Rocky Mountain Research Station, Missoula Fire Sciences Laboratory, Fire
> Chemistry Project, Remote Sensing Lab.
>
>
> > What are you using PostGIS for?
> We have a MODIS (satellite) Direct Broadcast receiving station here. It
> monitors active fires in near-real time across the Western U.S., Canada,
> and Mexico. We use a postgis database to store active fire (thermal
> anomaly) detections and burnscar (charred veg) detections, and filter and
> combine them into progressing fire perimeters.
>
> Additionally, we have another postgis database as a geospatial datastore
> and backend for portraying atmospheric chemistry data.
> http://smoke-fire.us:8080/geoserver/data/quickWMS/alex_devel.jsp
>
>
> > Why did you choose PostGIS?
> It's fun and affordable! Linux-friendly, highly configurable and flexible,
> many programming options available.
>
>
> > What makes you happy about your PostGIS installation?
> It works every day as expected.
>
>
> > What makes you unhappy about your PostGIS installation?
> Hm. Not a whole lot, to be honest.
>
>
>
> Paul Ramsey
> <pramsey at refracti
> ons.net> To
> Sent by: PostGIS Users Discussion
> postgis-users-bou <postgis-users at postgis.refractions.
> nces at postgis.refr net>
> actions.net cc
>
> Subject
> 07/12/2006 11:35 [postgis-users] Who is Using
> AM PostGIS? For What?
>
>
> Please respond to
> PostGIS Users
> Discussion
> <postgis-users at po
> stgis.refractions
> .net>
>
>
>
>
>
>
>
> It is that time of year again (conference time) so I am going to ask my
> annual question(s):
>
> Who are you?
> What are you using PostGIS for?
> Why did you choose PostGIS?
> What makes you happy about your PostGIS installation?
> What makes you unhappy about your PostGIS installation?
>
> Those who want to remain in confidence, but still want to make their
> feelings known, please respond to me directly.
>
> I am going to be writing up a handful of PostGIS users into full
> white-papers for the web site and my own conference talks, so please, if
> you think you have a compelling PostGIS story, start by giving us a
> summary here on the list!
>
> I love hearing these stories!
>
> Paul
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
> ------------------------------
>
> Message: 7
> Date: Fri, 14 Jul 2006 10:36:58 +0930
> From: Stephen Davies <sdc at sdc.com.au>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: postgis-users at postgis.refractions.net
> Message-ID: <200607141036.59080.sdc at sdc.com.au>
> Content-Type: text/plain; charset="us-ascii"
>
> Who are you?
>
> Stephen Davies Consulting - information management consulting.
>
> What are you using PostGIS for?
>
> Primary PostGIS application is the management of soil quality (moisture,
> salinity etc) and weather data collection and analysis.
> Cadastral, irrigation unit and logger/probe data is stored in a PostGIS
> database and associated metric data automatically uploaded for storage
> and analysis.
> Analysis results are fed back to farmers etc via Grace plots and/or
> colour-coded Mapserver web pages and pages specially formatted for
> reception by 3G mobile phones (so that farmers out in the paddocks can
> review status in (almost) real time.)
> The "standard" Mapserver pages are also queryable to display
> metrics/analyses for selected locations.
>
> The application also uses SMS messaging to turn water pumps on/off based
> on the metric analyses.
>
> Why did you choose PostGIS?
>
> I am a long-term PostgreSQL user so PostGIS was an obvious first choice.
> Nothing I have seen since has cast any shadow on that decision.
>
> What makes you happy about your PostGIS installation?
>
> All of the GIS functions and capabilities that I need are there and
> perform well.
>
> What makes you unhappy about your PostGIS installation?
>
> I am still not confident that I know how to best handle data from
> multiple zones/SRIDs given the single-SRID/table constraint of PostGIS.
> --
> ========================================================================
> This email is for the person(s) identified above, and is confidential to
> the sender and the person(s). No one else is authorised to use or
> disseminate this email or its contents.
>
> Stephen Davies Consulting Voice: 08-8177 1595
> Adelaide, South Australia. Fax: 08-8177 0133
> Computing & Network solutions. Mobile:0403 0405 83
>
>
> ------------------------------
>
> Message: 8
> Date: Thu, 13 Jul 2006 18:07:36 -0700
> From: Josh Livni <josh at livniconsulting.com>
> Subject: [postgis-users] optimize: count up points near a point
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B6EE58.8040501 at livniconsulting.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> I checked a few threads on this topic, but didn't come across anything
> that helped me make my query a lot quicker. So, I post here asking for
> your advice:
>
> I have about a million parcels, some of which are missing an attribute.
> I'd like to query parcels nearby (say within 1km) to get some stats on
> their value for the attribute.
>
> Right now it takes about 40-60 seconds to report on the nearby parcels -
> since over 100k parcels are missing the attribute, this is a problem...
>
> ---BEGIN QUERY---
> SELECT count(*) as count,
> avg(asr_impr) as avg,
> sum(asr_impr) as sum,
> max(asr_impr) as max,
> min(asr_impr) as min
> FROM parcels as p
> WHERE attribute > 0 and other_attribute = 11
> AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3
> 1955364.5)'::geometry,102646))
> AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3
> 1955364.5)'::geometry,102646)) < 3280.8
> ---END QUERY---
>
> I have a btree index on the attribute and other_attribute columns, and a
> gist index on the parcels centroid(the_geom) column.
>
>
>
> Any help is greatly appreciated,
>
> Thanks,
>
> -Josh
>
>
>
>
>
>
>
> ------------------------------
>
> Message: 9
> Date: Thu, 13 Jul 2006 19:27:17 -0600
> From: Michael Fuhr <mike at fuhr.org>
> Subject: Re: [postgis-users] optimize: count up points near a point
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <20060714012717.GA90482 at winnie.fuhr.org>
> Content-Type: text/plain; charset=us-ascii
>
> On Thu, Jul 13, 2006 at 06:07:36PM -0700, Josh Livni wrote:
> > I have about a million parcels, some of which are missing an attribute.
> > I'd like to query parcels nearby (say within 1km) to get some stats on
> > their value for the attribute.
> >
> > Right now it takes about 40-60 seconds to report on the nearby parcels -
> > since over 100k parcels are missing the attribute, this is a problem...
> >
> > ---BEGIN QUERY---
> > SELECT count(*) as count,
> > avg(asr_impr) as avg,
> > sum(asr_impr) as sum,
> > max(asr_impr) as max,
> > min(asr_impr) as min
> > FROM parcels as p
> > WHERE attribute > 0 and other_attribute = 11
> > AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3
> > 1955364.5)'::geometry,102646))
> > AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3
> > 1955364.5)'::geometry,102646)) < 3280.8
> > ---END QUERY---
> >
> > I have a btree index on the attribute and other_attribute columns, and a
> > gist index on the parcels centroid(the_geom) column.
>
> What does EXPLAIN ANALYZE show for this query? I don't think an
> index on centroid(the_geom) is going to be used, but an index on
> expand(the_geom, 3280.8) should be if you have one. You might also
> be able to speed up queries by creating another geometry column to
> hold the centroid, which you could populate automatically via a
> trigger (at the cost of slowing down inserts and updates).
>
> --
> Michael Fuhr
>
>
> ------------------------------
>
> Message: 10
> Date: Thu, 13 Jul 2006 18:57:06 -0700
> From: Josh Livni <josh at livniconsulting.com>
> Subject: Re: [postgis-users] optimize: count up points near a point
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B6F9F2.8020307 at livniconsulting.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Thanks for the good points Michael -- I'll definitely make a centroid
> geometry column (I only query this; never update).
>
> I'll also create index on the 'expand(centroid_geom),3280', since that
> seems to have sped it up a lot. Unfortunately my distance sometimes
> varies, and is not always a kilometer, but I still think it will be
> faster to just make a bunch of indices.
>
> Anyway, more to the point, as I was changing my query to take advantage
> of my new centroid geometry column, I realized my massive typo: I was
> using "(expand(p.the_geom,3280.8) && ..." instead of
> "(expand(centroid(p.the_geom),3280.8) && ..."
>
> At first glance, fixing this up, combined with your suggestion of
> indexing the expanded centroid, seems to speed it up an order of
> magnitude or so.
>
>
>
> -Josh
>
> PS I think I'll also make it "(expand(p.the_geom,3280.8 + some extra
> distance) && ... " and index that too, to be sure I'm picking up the
> points I want, but this should still be much much faster than expanding
> the polygon w/no index
>
>
> Michael Fuhr wrote:
> > On Thu, Jul 13, 2006 at 06:07:36PM -0700, Josh Livni wrote:
> >> I have about a million parcels, some of which are missing an attribute.
> >> I'd like to query parcels nearby (say within 1km) to get some stats on
> >> their value for the attribute.
> >>
> >> Right now it takes about 40-60 seconds to report on the nearby parcels
> -
> >> since over 100k parcels are missing the attribute, this is a problem...
> >>
> >> ---BEGIN QUERY---
> >> SELECT count(*) as count,
> >> avg(asr_impr) as avg,
> >> sum(asr_impr) as sum,
> >> max(asr_impr) as max,
> >> min(asr_impr) as min
> >> FROM parcels as p
> >> WHERE attribute > 0 and other_attribute = 11
> >> AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3
> >> 1955364.5)'::geometry,102646))
> >> AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3
> >> 1955364.5)'::geometry,102646)) < 3280.8
> >> ---END QUERY---
> >>
> >> I have a btree index on the attribute and other_attribute columns, and
> a
> >> gist index on the parcels centroid(the_geom) column.
> >
> > What does EXPLAIN ANALYZE show for this query? I don't think an
> > index on centroid(the_geom) is going to be used, but an index on
> > expand(the_geom, 3280.8) should be if you have one. You might also
> > be able to speed up queries by creating another geometry column to
> > hold the centroid, which you could populate automatically via a
> > trigger (at the cost of slowing down inserts and updates).
> >
>
>
>
>
> ------------------------------
>
> Message: 11
> Date: Thu, 13 Jul 2006 22:22:11 -0400
> From: Stephen Woodbridge <woodbri at swoodbridge.com>
> Subject: Re: [postgis-users] optimize: count up points near a point
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B6FFD3.6050205 at swoodbridge.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Josh,
>
> The expand of point forms a square and the distance forms a circle
> within the square. If your use case allows it and you don't care about
> trimming the corners off the square, skip the distance calc all
> together. Which will really speed things up.
>
> -Steve W
>
> Josh Livni wrote:
> > Thanks for the good points Michael -- I'll definitely make a centroid
> > geometry column (I only query this; never update).
> >
> > I'll also create index on the 'expand(centroid_geom),3280', since that
> > seems to have sped it up a lot. Unfortunately my distance sometimes
> > varies, and is not always a kilometer, but I still think it will be
> > faster to just make a bunch of indices.
> >
> > Anyway, more to the point, as I was changing my query to take advantage
> > of my new centroid geometry column, I realized my massive typo: I was
> > using "(expand(p.the_geom,3280.8) && ..." instead of
> > "(expand(centroid(p.the_geom),3280.8) && ..."
> >
> > At first glance, fixing this up, combined with your suggestion of
> > indexing the expanded centroid, seems to speed it up an order of
> > magnitude or so.
> >
> >
> >
> > -Josh
> >
> > PS I think I'll also make it "(expand(p.the_geom,3280.8 + some extra
> > distance) && ... " and index that too, to be sure I'm picking up the
> > points I want, but this should still be much much faster than expanding
> > the polygon w/no index
> >
> >
> > Michael Fuhr wrote:
> >> On Thu, Jul 13, 2006 at 06:07:36PM -0700, Josh Livni wrote:
> >>> I have about a million parcels, some of which are missing an
> >>> attribute. I'd like to query parcels nearby (say within 1km) to get
> >>> some stats on their value for the attribute.
> >>>
> >>> Right now it takes about 40-60 seconds to report on the nearby
> >>> parcels - since over 100k parcels are missing the attribute, this is
> >>> a problem...
> >>>
> >>> ---BEGIN QUERY---
> >>> SELECT count(*) as count,
> >>> avg(asr_impr) as avg,
> >>> sum(asr_impr) as sum,
> >>> max(asr_impr) as max,
> >>> min(asr_impr) as min
> >>> FROM parcels as p
> >>> WHERE attribute > 0 and other_attribute = 11
> >>> AND (expand(p.the_geom,3280.8) && setsrid('POINT(6296272.3
> >>> 1955364.5)'::geometry,102646))
> >>> AND distance(centroid(p.the_geom), setsrid('POINT(6296272.3
> >>> 1955364.5)'::geometry,102646)) < 3280.8
> >>> ---END QUERY---
> >>>
> >>> I have a btree index on the attribute and other_attribute columns,
> >>> and a gist index on the parcels centroid(the_geom) column.
> >>
> >> What does EXPLAIN ANALYZE show for this query? I don't think an
> >> index on centroid(the_geom) is going to be used, but an index on
> >> expand(the_geom, 3280.8) should be if you have one. You might also
> >> be able to speed up queries by creating another geometry column to
> >> hold the centroid, which you could populate automatically via a
> >> trigger (at the cost of slowing down inserts and updates).
> >>
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
>
> ------------------------------
>
> Message: 12
> Date: Fri, 14 Jul 2006 09:19:32 +0200
> From: Bernhard Reimar Hoefle <Bernhard.Hoefle at uibk.ac.at>
> Subject: [postgis-users] Re: postgis-users Digest, Vol 45, Issue 13
> To: postgis-users at postgis.refractions.net
> Message-ID: <1152861572.44b7458439082 at web-mail2.uibk.ac.at>
> Content-Type: text/plain; charset=ISO-8859-1
>
> > Who are you?
> Bernhard Hoefle, PhD student at Institute of Geography
> (www.uibk.ac.at/geographie) and at alpS - Centre for Natural Hazard
> Management
> (www.alps-gmbh.com). I'm working in a research project dealing with
> Airborne
> Laser Scanning (ALS) Data for Natural Hazard purposes.
>
> > What are you using PostGIS for?
>
> Storing ALS point clouds (up to 1 Billion points/database).
>
> > Why did you choose PostGIS?
>
> Because I worked with PostgreSQL before and was (I'm still) very happy
> with it.
> It has a quite large collection of GIS functionality that I could use. The
> community is active and competent in nearly every field of interest.
>
> > What makes you happy about your PostGIS installation?
>
> Stable! Easy-to-use with GRASS and QGIS.
>
> > What makes you unhappy about your PostGIS installation?
>
> Poor documentation about programming/extending C functions of PostGIS.
>
> I'll add some wishes to the list ;-)
>
>
> ------------------------------
>
> Message: 13
> Date: Fri, 14 Jul 2006 11:46:02 +0200
> From: "Johan V" <aragorn_jv at yahoo.com>
> Subject: [postgis-users] ERROR: Operation on two GEOMETRIES with
> different SRIDs
> To: postgis-users at postgis.refractions.net
> Message-ID:
> <6069fb410607140246kb67f60bxcf184b6dcf0e4606 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hello,
>
> I'm having some problems with a table 'reflijst' I created recently in
> a Postgresql 7.3.4 - Postgis 0.8.2 - geos 1.0 database. If is a table
> with SRID equals 103300 and geometrytype MULTIPOLYGON. I added the
> the_geom field with the sql:
>
> select
> AddGeometryColumn('public','reflijst','the_geom','103300','MULTIPOLYGON',2);
>
> ==> Which created the neccessary column and corresponding rule in the
> geometry_columns table:
>
> (COLUMN HEADERS) oid f_table_catalog f_table_schema f_table_name
> f_geometry_column coord_dimension srid "type" attrelid varattnum stats
>
> (ADDED CONTENT)
>
> "13636488";"''";"public";"reflijst";"the_geom";"2";"103300";"MULTIPOLYGON";"13577009";"17";""
>
> ==> And the correct CHECK CONTRAINTS ARE CREATED FOR THAT TABLE
> CONSTRAINT "$1" CHECK ((srid(the_geom) = 103300))
> CONSTRAINT "$2" CHECK (((geometrytype(the_geom) = 'POINT'::text) OR
> (the_geom IS NULL)))
>
> Now am i trying to do the next sql statement on this table:
>
> SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
> ((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274
> 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'))
>
> Than I get the response: ERROR: Operation on two GEOMETRIES with
> different SRIDs.
>
> If I change 'POLYGON((...))' into 'SRID=103300;POLYGON((..))' in the
> sql statement than I get a correct response, it is the expected
> columns.
> Or if I do not change the sql statement, but change the srid in the
> table to -1 (i.e. UNKNOWN) and I change of course also the CHECK
> CONSTRAINTS and the corresponding srid column in the geometry_columns
> table, I also get a correct response.
>
> What am I doing wrong? What do I have to change in the table or the
> database that this still works! Because everthing in the table looks
> correct. I can even view the data in the table with the products JUMP
> and Quantum GIS. And I can execute the same sql statement on another
> table without any problems (it is a table with point geometry).
>
> Greetz,
> Johan.
>
>
> ------------------------------
>
> Message: 14
> Date: Fri, 14 Jul 2006 10:57:50 +0100
> From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
> Subject: RE: [postgis-users] ERROR: Operation on two GEOMETRIES with
> differentSRIDs
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <200607140957.k6E9vrQ24541 at webbased16.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > bounces at postgis.refractions.net] On Behalf Of Johan V
> > Sent: 14 July 2006 10:46
> > To: postgis-users at postgis.refractions.net
> > Subject: [postgis-users] ERROR: Operation on two GEOMETRIES with
> > differentSRIDs
>
> (cut)
>
> > Now am i trying to do the next sql statement on this table:
> >
> >
> >
> > Than I get the response: ERROR: Operation on two GEOMETRIES with
> > different SRIDs.
>
>
> Hi Johan,
>
> The issue here is that it is the geometry you are creating on the fly is
> constructed with a SRID of -1 because it has not yet been tied to a
> coordinate system - your table definition and the constraints that go with
> it are fine. The way to handle this is to use setSRID()/GeometryFromText()
> to assign the same coordinate system to your polygon when you construct
> it,
> e.g.
>
> SELECT the_geom FROM reflijst WHERE ((the_geom && GeomFromText(
> 'POLYGON((33595.443392445566 15067.465440617714, 281187.7093687274
> 15067.465440617714, 281187.7093687274 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))', 103300)))
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
>
>
>
> ------------------------------
>
> Message: 15
> Date: Fri, 14 Jul 2006 13:46:02 +0200
> From: "Johan V" <jvhoppli at gmail.com>
> Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES with
> differentSRIDs
> To: postgis-users at postgis.refractions.net
> Message-ID:
> <6069fb410607140446g7021b80dn51f4aaed215d1b25 at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Mark and others,
>
> Yes the geometry created on the fly is -1, but this same sql statement
> (with the on the fly created geometry) does work on another table with
> SRID=103300, but not on this one.
>
> I need this specific sql statement because it is a statement that is
> issued by a software product that makes a WFS/WMS service (GIS Service
> over the internet) of this table.
>
> So do I have to change or check some thing in the table to be able to
> use this kind of sql statement:
>
> SELECT the_geom FROM table_x WHERE ((the_geom && 'POLYGON((...))')).
>
> Greetz,
> Johan.
>
>
> ------------------------------
>
> Message: 16
> Date: Fri, 14 Jul 2006 14:18:58 +0100
> From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
> Subject: RE: [postgis-users] ERROR: Operation on two GEOMETRIES
> withdifferentSRIDs
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <200607141319.k6EDJ2Q01070 at webbased16.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > bounces at postgis.refractions.net] On Behalf Of Johan V
> > Sent: 14 July 2006 12:46
> > To: postgis-users at postgis.refractions.net
> > Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> > withdifferentSRIDs
> >
> > Hi Mark and others,
> >
> > Yes the geometry created on the fly is -1, but this same sql statement
> > (with the on the fly created geometry) does work on another table with
> > SRID=103300, but not on this one.
> >
> > I need this specific sql statement because it is a statement that is
> > issued by a software product that makes a WFS/WMS service (GIS Service
> > over the internet) of this table.
> >
> > So do I have to change or check some thing in the table to be able to
> > use this kind of sql statement:
> >
> > SELECT the_geom FROM table_x WHERE ((the_geom && 'POLYGON((...))')).
> >
> > Greetz,
> > Johan.
>
>
> Hi Johan,
>
> Hmmm that makes it a bit more tricky. Unfortunately the SQL being issued
> is
> relying on the implicit cast in PostgreSQL rather than using
> GeomFromText()/setSRID() as documented and so its behaviour may change
> across PostgreSQL/PostGIS versions - if you do get a chance to update the
> SQL in your client software, I would highly recommend it.
>
> As for the symptoms you are seeing, I am slightly baffled - can you post
> the
> output of \d <table> for one of your tables that works and your new one
> that
> doesn't, the exact SELECT queries being executed on each table, and can
> you
> confirm that both tables are in the same database running the same version
> of PostGIS?
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
>
>
>
> ------------------------------
>
> Message: 17
> Date: Fri, 14 Jul 2006 15:30:58 +0200
> From: "Johan V" <aragorn_jv at yahoo.com>
> Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> withdifferentSRIDs
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <6069fb410607140630k6d474008gb610c10fe77e84dc at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Mark,
>
> Here are the descriptions of the tables (in the same database running
> POSTGIS 0.8.2):
>
> [database_x]=# \d reflijst
> Table "public.reflijst"
> Column | Type | Modifiers
> -----------+-------------------+-----------
> artid | integer |
> titre | text |
> annee | integer |
> volume | character varying |
> firstpage | character varying |
> auteurs | text |
> publica | text |
> strat | character varying |
> domg_ol | character varying |
> xlambert | bigint |
> ylambert | bigint |
> numcarte | integer |
> the_geom | geometry |
> Check constraints: "$1" (srid(the_geom) = 103300)
> "$2" ((geometrytype(the_geom) =
> 'MULTIPOLYGON'::text) OR (the_geom IS NULL))
>
>
> [database_x]=# \d gnosbgm
> Table "public.gnosbgm"
> Column | Type | Modifiers
> ------------+------------------------+-----------
> id | numeric(28,6) |
> gemeente | character varying(254) |
> x | numeric(28,6) |
> y | numeric(28,6) |
> z | numeric(28,6) |
> kaart | character varying(254) |
> logdiepte | numeric(28,6) |
> diepte | numeric(28,6) |
> bgdnr | character varying(254) |
> bgdcode | character varying(254) |
> refer | character varying(254) |
> hotlink | character varying(254) |
> imagepath1 | character varying(254) |
> logcomp | character varying(254) |
> date | character varying(10) |
> tert_plio | character varying(1) |
> tert_mio | character varying(1) |
> tert_paleo | character varying(1) |
> tert_eoc | character varying(1) |
> tert_oligo | character varying(1) |
> primair | character varying(1) |
> secundair | character varying(1) |
> tertiair | character varying(1) |
> quartair | character varying(1) |
> the_geom | geometry |
> Indexes: sidx_gnosbgm gist (the_geom)
> Check constraints: "$1" (srid(the_geom) = 103300)
> "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> (the_geom IS NULL))
>
> And the two sql statements:
>
> SELECT the_geom FROM gnosbgm WHERE ((the_geom && 'POLYGON
> ((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274
> 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'))
>
> SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
> ((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274
> 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'))
>
> They are just the same, only the first give me the required answer!
>
> Grz,
> Johan
>
> 2006/7/14, Mark Cave-Ayland <m.cave-ayland at webbased.co.uk>:
> >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > > bounces at postgis.refractions.net] On Behalf Of Johan V
> > > Sent: 14 July 2006 12:46
> > > To: postgis-users at postgis.refractions.net
> > > Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> > > withdifferentSRIDs
> > >
> > > Hi Mark and others,
> > >
> > > Yes the geometry created on the fly is -1, but this same sql statement
> > > (with the on the fly created geometry) does work on another table with
> > > SRID=103300, but not on this one.
> > >
> > > I need this specific sql statement because it is a statement that is
> > > issued by a software product that makes a WFS/WMS service (GIS Service
> > > over the internet) of this table.
> > >
> > > So do I have to change or check some thing in the table to be able to
> > > use this kind of sql statement:
> > >
> > > SELECT the_geom FROM table_x WHERE ((the_geom && 'POLYGON((...))')).
> > >
> > > Greetz,
> > > Johan.
> >
> >
> > Hi Johan,
> >
> > Hmmm that makes it a bit more tricky. Unfortunately the SQL being issued
> is
> > relying on the implicit cast in PostgreSQL rather than using
> > GeomFromText()/setSRID() as documented and so its behaviour may change
> > across PostgreSQL/PostGIS versions - if you do get a chance to update
> the
> > SQL in your client software, I would highly recommend it.
> >
> > As for the symptoms you are seeing, I am slightly baffled - can you post
> the
> > output of \d <table> for one of your tables that works and your new one
> that
> > doesn't, the exact SELECT queries being executed on each table, and can
> you
> > confirm that both tables are in the same database running the same
> version
> > of PostGIS?
> >
> >
> > Kind regards,
> >
> > Mark.
> >
> > ------------------------
> > WebBased Ltd
> > 17 Research Way
> > Plymouth
> > PL6 8BT
> >
> > T: +44 (0)1752 797131
> > F: +44 (0)1752 791023
> >
> > http://www.webbased.co.uk
> > http://www.infomapper.com
> > http://www.swtc.co.uk
> >
> > This email and any attachments are confidential to the intended
> recipient
> > and may also be privileged. If you are not the intended recipient please
> > delete it from your system and notify the sender. You should not copy it
> or
> > use it for any purpose nor disclose or distribute its contents to any
> other
> > person.
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 18
> Date: Fri, 14 Jul 2006 14:42:17 +0100
> From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
> Subject: RE: [postgis-users] ERROR: Operation on two
> GEOMETRIESwithdifferentSRIDs
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <200607141342.k6EDgKQ01681 at webbased16.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > bounces at postgis.refractions.net] On Behalf Of Johan V
> > Sent: 14 July 2006 14:31
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] ERROR: Operation on two
> > GEOMETRIESwithdifferentSRIDs
> >
> > Hi Mark,
> >
> > Here are the descriptions of the tables (in the same database running
> > POSTGIS 0.8.2):
> >
> > [database_x]=# \d reflijst
> > Table "public.reflijst"
> > Column | Type | Modifiers
> > -----------+-------------------+-----------
> > artid | integer |
> > titre | text |
> > annee | integer |
> > volume | character varying |
> > firstpage | character varying |
> > auteurs | text |
> > publica | text |
> > strat | character varying |
> > domg_ol | character varying |
> > xlambert | bigint |
> > ylambert | bigint |
> > numcarte | integer |
> > the_geom | geometry |
> > Check constraints: "$1" (srid(the_geom) = 103300)
> > "$2" ((geometrytype(the_geom) =
> > 'MULTIPOLYGON'::text) OR (the_geom IS NULL))
> >
> >
> > [database_x]=# \d gnosbgm
> > Table "public.gnosbgm"
> > Column | Type | Modifiers
> > ------------+------------------------+-----------
> > id | numeric(28,6) |
> > gemeente | character varying(254) |
> > x | numeric(28,6) |
> > y | numeric(28,6) |
> > z | numeric(28,6) |
> > kaart | character varying(254) |
> > logdiepte | numeric(28,6) |
> > diepte | numeric(28,6) |
> > bgdnr | character varying(254) |
> > bgdcode | character varying(254) |
> > refer | character varying(254) |
> > hotlink | character varying(254) |
> > imagepath1 | character varying(254) |
> > logcomp | character varying(254) |
> > date | character varying(10) |
> > tert_plio | character varying(1) |
> > tert_mio | character varying(1) |
> > tert_paleo | character varying(1) |
> > tert_eoc | character varying(1) |
> > tert_oligo | character varying(1) |
> > primair | character varying(1) |
> > secundair | character varying(1) |
> > tertiair | character varying(1) |
> > quartair | character varying(1) |
> > the_geom | geometry |
> > Indexes: sidx_gnosbgm gist (the_geom)
> > Check constraints: "$1" (srid(the_geom) = 103300)
> > "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> > (the_geom IS NULL))
> >
> > And the two sql statements:
> >
> > SELECT the_geom FROM gnosbgm WHERE ((the_geom && 'POLYGON
> > ((33595.443392445566 15067.465440617714,281187.7093687274
> > 15067.465440617714,281187.7093687274
> > 254932.5345593823,33595.443392445566
> > 254932.5345593823,33595.443392445566 15067.465440617714))'))
> >
> > SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
> > ((33595.443392445566 15067.465440617714,281187.7093687274
> > 15067.465440617714,281187.7093687274
> > 254932.5345593823,33595.443392445566
> > 254932.5345593823,33595.443392445566 15067.465440617714))'))
> >
> > They are just the same, only the first give me the required answer!
> >
> > Grz,
> > Johan
>
>
> Hi Johan,
>
> I think that the problem is being caused by the fact that in older
> versions
> of PostGIS, index scans ignored the SRID of the geometry during
> comparisons
> (they don't have a RECHECK clause) where as sequential scans did take them
> into account. My guess is that if you force an index scan on your reflijst
> table, your query will start to work. Can you try the following in a psql
> session:
>
>
> CREATE INDEX sidx_reflijst ON reflijst USING gist (the_geom
> gist_geometry_ops);
>
> VACUUM ANALYZE reflijst;
>
> SET enable_seqscan = 'f';
>
> SELECT the_geom FROM reflijst WHERE ((the_geom &&
> 'POLYGON((33595.443392445566 15067.465440617714,281187.7093687274
> 15067.465440617714,281187.7093687274 254932.5345593823,33595.443392445566
> 254932.5345593823,33595.443392445566 15067.465440617714))'));
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
>
>
>
> ------------------------------
>
> Message: 19
> Date: Fri, 14 Jul 2006 15:49:58 +0200
> From: "Johan V" <aragorn_jv at yahoo.com>
> Subject: Re: [postgis-users] ERROR: Operation on two
> GEOMETRIESwithdifferentSRIDs
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <6069fb410607140649q63723449x2a87e6032a44f27b at mail.gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Thanks that seems to work,
>
> greetz,
> Johan.
>
> 2006/7/14, Mark Cave-Ayland <m.cave-ayland at webbased.co.uk>:
> >
> > > -----Original Message-----
> > > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > > bounces at postgis.refractions.net] On Behalf Of Johan V
> > > Sent: 14 July 2006 14:31
> > > To: PostGIS Users Discussion
> > > Subject: Re: [postgis-users] ERROR: Operation on two
> > > GEOMETRIESwithdifferentSRIDs
> > >
> > > Hi Mark,
> > >
> > > Here are the descriptions of the tables (in the same database running
> > > POSTGIS 0.8.2):
> > >
> > > [database_x]=# \d reflijst
> > > Table "public.reflijst"
> > > Column | Type | Modifiers
> > > -----------+-------------------+-----------
> > > artid | integer |
> > > titre | text |
> > > annee | integer |
> > > volume | character varying |
> > > firstpage | character varying |
> > > auteurs | text |
> > > publica | text |
> > > strat | character varying |
> > > domg_ol | character varying |
> > > xlambert | bigint |
> > > ylambert | bigint |
> > > numcarte | integer |
> > > the_geom | geometry |
> > > Check constraints: "$1" (srid(the_geom) = 103300)
> > > "$2" ((geometrytype(the_geom) =
> > > 'MULTIPOLYGON'::text) OR (the_geom IS NULL))
> > >
> > >
> > > [database_x]=# \d gnosbgm
> > > Table "public.gnosbgm"
> > > Column | Type | Modifiers
> > > ------------+------------------------+-----------
> > > id | numeric(28,6) |
> > > gemeente | character varying(254) |
> > > x | numeric(28,6) |
> > > y | numeric(28,6) |
> > > z | numeric(28,6) |
> > > kaart | character varying(254) |
> > > logdiepte | numeric(28,6) |
> > > diepte | numeric(28,6) |
> > > bgdnr | character varying(254) |
> > > bgdcode | character varying(254) |
> > > refer | character varying(254) |
> > > hotlink | character varying(254) |
> > > imagepath1 | character varying(254) |
> > > logcomp | character varying(254) |
> > > date | character varying(10) |
> > > tert_plio | character varying(1) |
> > > tert_mio | character varying(1) |
> > > tert_paleo | character varying(1) |
> > > tert_eoc | character varying(1) |
> > > tert_oligo | character varying(1) |
> > > primair | character varying(1) |
> > > secundair | character varying(1) |
> > > tertiair | character varying(1) |
> > > quartair | character varying(1) |
> > > the_geom | geometry |
> > > Indexes: sidx_gnosbgm gist (the_geom)
> > > Check constraints: "$1" (srid(the_geom) = 103300)
> > > "$2" ((geometrytype(the_geom) = 'POINT'::text) OR
> > > (the_geom IS NULL))
> > >
> > > And the two sql statements:
> > >
> > > SELECT the_geom FROM gnosbgm WHERE ((the_geom && 'POLYGON
> > > ((33595.443392445566 15067.465440617714,281187.7093687274
> > > 15067.465440617714,281187.7093687274
> > > 254932.5345593823,33595.443392445566
> > > 254932.5345593823,33595.443392445566 15067.465440617714))'))
> > >
> > > SELECT the_geom FROM reflijst WHERE ((the_geom && 'POLYGON
> > > ((33595.443392445566 15067.465440617714,281187.7093687274
> > > 15067.465440617714,281187.7093687274
> > > 254932.5345593823,33595.443392445566
> > > 254932.5345593823,33595.443392445566 15067.465440617714))'))
> > >
> > > They are just the same, only the first give me the required answer!
> > >
> > > Grz,
> > > Johan
> >
> >
> > Hi Johan,
> >
> > I think that the problem is being caused by the fact that in older
> versions
> > of PostGIS, index scans ignored the SRID of the geometry during
> comparisons
> > (they don't have a RECHECK clause) where as sequential scans did take
> them
> > into account. My guess is that if you force an index scan on your
> reflijst
> > table, your query will start to work. Can you try the following in a
> psql
> > session:
> >
> >
> > CREATE INDEX sidx_reflijst ON reflijst USING gist (the_geom
> > gist_geometry_ops);
> >
> > VACUUM ANALYZE reflijst;
> >
> > SET enable_seqscan = 'f';
> >
> > SELECT the_geom FROM reflijst WHERE ((the_geom &&
> > 'POLYGON((33595.443392445566 15067.465440617714,281187.7093687274
> > 15067.465440617714,281187.7093687274 254932.5345593823,
> 33595.443392445566
> > 254932.5345593823,33595.443392445566 15067.465440617714))'));
> >
> >
> > Kind regards,
> >
> > Mark.
> >
> > ------------------------
> > WebBased Ltd
> > 17 Research Way
> > Plymouth
> > PL6 8BT
> >
> > T: +44 (0)1752 797131
> > F: +44 (0)1752 791023
> >
> > http://www.webbased.co.uk
> > http://www.infomapper.com
> > http://www.swtc.co.uk
> >
> > This email and any attachments are confidential to the intended
> recipient
> > and may also be privileged. If you are not the intended recipient please
> > delete it from your system and notify the sender. You should not copy it
> or
> > use it for any purpose nor disclose or distribute its contents to any
> other
> > person.
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
>
>
> ------------------------------
>
> Message: 20
> Date: Fri, 14 Jul 2006 10:51:00 -0300
> From: Ezequias Rodrigues da Rocha <ezequias at recife.pe.gov.br>
> Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> withdifferentSRIDs
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B7A144.5040300 at recife.pe.gov.br>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Something like this ?
>
> *update mul.lotes set geometry = setsrid(geometry, 29185); *
>
> where geometry is the geometry field of my geographic table. ;-)
>
> It works for me but I do not know how to recreate the constraint I must
> delete.
>
> Can anyone help me :
>
> CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = 29185)
>
> Regards.
> Ezequias
>
> Mark Cave-Ayland escreveu:
> >> -----Original Message-----
> >> From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> >> bounces at postgis.refractions.net] On Behalf Of Johan V
> >> Sent: 14 July 2006 12:46
> >> To: postgis-users at postgis.refractions.net
> >> Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> >> withdifferentSRIDs
> >>
> >> Hi Mark and others,
> >>
> >> Yes the geometry created on the fly is -1, but this same sql statement
> >> (with the on the fly created geometry) does work on another table with
> >> SRID=103300, but not on this one.
> >>
> >> I need this specific sql statement because it is a statement that is
> >> issued by a software product that makes a WFS/WMS service (GIS Service
> >> over the internet) of this table.
> >>
> >> So do I have to change or check some thing in the table to be able to
> >> use this kind of sql statement:
> >>
> >> SELECT the_geom FROM table_x WHERE ((the_geom && 'POLYGON((...))')).
> >>
> >> Greetz,
> >> Johan.
> >>
> >
> >
> > Hi Johan,
> >
> > Hmmm that makes it a bit more tricky. Unfortunately the SQL being issued
> is
> > relying on the implicit cast in PostgreSQL rather than using
> > GeomFromText()/setSRID() as documented and so its behaviour may change
> > across PostgreSQL/PostGIS versions - if you do get a chance to update
> the
> > SQL in your client software, I would highly recommend it.
> >
> > As for the symptoms you are seeing, I am slightly baffled - can you post
> the
> > output of \d <table> for one of your tables that works and your new one
> that
> > doesn't, the exact SELECT queries being executed on each table, and can
> you
> > confirm that both tables are in the same database running the same
> version
> > of PostGIS?
> >
> >
> > Kind regards,
> >
> > Mark.
> >
> > ------------------------
> > WebBased Ltd
> > 17 Research Way
> > Plymouth
> > PL6 8BT
> >
> > T: +44 (0)1752 797131
> > F: +44 (0)1752 791023
> >
> > http://www.webbased.co.uk
> > http://www.infomapper.com
> > http://www.swtc.co.uk
> >
> > This email and any attachments are confidential to the intended
> recipient
> > and may also be privileged. If you are not the intended recipient please
> > delete it from your system and notify the sender. You should not copy it
> or
> > use it for any purpose nor disclose or distribute its contents to any
> other
> > person.
> >
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> >
>
> --
> Ezequias Rodrigues da Rocha
> http://ezequiasrocha.blogspot.com
> msn:ezequias at hotmail.com
> "the worst of democracies is still better than the best of dictatorship"
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060714/f12da7b6/attachment-0001.html
>
> ------------------------------
>
> Message: 21
> Date: Fri, 14 Jul 2006 10:04:05 -0400
> From: "Robert Burgholzer" <rburghol at chesapeakebay.net>
> Subject: RE: [postgis-users] Who is Using PostGIS? For What?
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Message-ID:
> <C9D51E2209AFD04F93E4E2E4CD6E497901A1278F at mail1.chesapeakebay.net>
> Content-Type: text/plain; charset="us-ascii"
>
> Who are you?
> Robert Burgholzer, Non-point Source Data Analyst, Chesapeake Bay Program
> What are you using PostGIS for?
> As a spatial analysis tool, and GIS engine for a web-based non-point
> source pollution model of the Chesapeake Bay watershed (64,000 square
> miles)
> Why did you choose PostGIS?
> Open Source, full geo-processing capability
> What makes you happy about your PostGIS installation?
> Flexibility, utility, and general all-around awesome-ness of the
> product.
> What makes you unhappy about your PostGIS installation?
> Wish there was more support for read-write in AcrGIS >= 8
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
> Ramsey
> Sent: Wednesday, July 12, 2006 1:36 PM
> To: PostGIS Users Discussion
> Subject: [postgis-users] Who is Using PostGIS? For What?
>
>
> It is that time of year again (conference time) so I am going to ask my
> annual question(s):
>
> Who are you?
> What are you using PostGIS for?
> Why did you choose PostGIS?
> What makes you happy about your PostGIS installation?
> What makes you unhappy about your PostGIS installation?
>
> Those who want to remain in confidence, but still want to make their
> feelings known, please respond to me directly.
>
> I am going to be writing up a handful of PostGIS users into full
> white-papers for the web site and my own conference talks, so please, if
>
> you think you have a compelling PostGIS story, start by giving us a
> summary here on the list!
>
> I love hearing these stories!
>
> Paul
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> ------------------------------
>
> Message: 22
> Date: Fri, 14 Jul 2006 15:58:24 +0200
> From: Markus Schaber <schabi at logix-tt.com>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B7A300.6070507 at logix-tt.com>
> Content-Type: text/plain; charset=ISO-8859-15
>
> Hi, Paul,
>
> Paul Ramsey wrote:
>
> > Who are you?
>
> Markus Schaber, GIS Software developer for Logical Tracking & Tracing
> International AG, Switzerland
>
> > What are you using PostGIS for?
>
> Storage and processing of tracking data.
>
> Storage and processing of geodata (Street network etc.) for map display.
>
> Storage and preprocessing of geodata for other applications (routing,
> geocoding etc.)
>
> > Why did you choose PostGIS?
>
> The per-CPU license for oracle spatial is more expensive than paying a
> developer to tweak PostGIS to our needs.
>
> > What makes you happy about your PostGIS installation?
>
> It works (most of the time :-)
>
> > What makes you unhappy about your PostGIS installation?
>
> Debian packages situation is somehow unstable.
>
> HTH,
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf. | Software Development GIS
>
> Fight against software patents in EU! www.ffii.org
> www.nosoftwarepatents.org
>
>
> ------------------------------
>
> Message: 23
> Date: Fri, 14 Jul 2006 16:42:02 +0100
> From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
> Subject: RE: [postgis-users] ERROR: Operation on
> twoGEOMETRIESwithdifferentSRIDs
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <200607141542.k6EFg7Q06367 at webbased16.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
> > -----Original Message-----
> > From: postgis-users-bounces at postgis.refractions.net [mailto:
> postgis-users-
> > bounces at postgis.refractions.net] On Behalf Of Johan V
> > Sent: 14 July 2006 14:50
> > To: PostGIS Users Discussion
> > Subject: Re: [postgis-users] ERROR: Operation on
> > twoGEOMETRIESwithdifferentSRIDs
> >
> > Thanks that seems to work,
> >
> > greetz,
> > Johan.
>
>
> Hi Johan,
>
> That's great news - but don't forget to correct the error in your client
> software as soon as you can - definitely PostGIS >= 1.0.0 won't like this,
> and in fact I don't even think you can upgrade to PostGIS 0.9 without
> these
> client queries failing :(
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
>
>
>
> ------------------------------
>
> Message: 24
> Date: Fri, 14 Jul 2006 16:47:08 +0100
> From: "Mark Cave-Ayland" <m.cave-ayland at webbased.co.uk>
> Subject: RE: [postgis-users] ERROR: Operation on twoGEOMETRIES
> withdifferentSRIDs
> To: "'PostGIS Users Discussion'"
> <postgis-users at postgis.refractions.net>
> Message-ID: <200607141547.k6EFlDQ06582 at webbased16.localdomain>
> Content-Type: text/plain; charset="us-ascii"
>
> Hi Ezequias,
>
>
>
> You should be able to find this information in the PostgreSQL
> documentation
> for ALTER TABLE at
> http://www.postgresql.org/docs/8.1/interactive/sql-altertable.html.
>
>
>
>
>
> Kind regards,
>
>
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
> _____
>
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
> Ezequias
> Rodrigues da Rocha
> Sent: 14 July 2006 14:51
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] ERROR: Operation on twoGEOMETRIES
> withdifferentSRIDs
>
>
>
> Something like this ?
>
> update mul.lotes set geometry = setsrid(geometry, 29185);
>
> where geometry is the geometry field of my geographic table. ;-)
>
> It works for me but I do not know how to recreate the constraint I must
> delete.
>
> Can anyone help me :
>
> CONSTRAINT enforce_srid_geometry CHECK (srid(geometry) = 29185)
>
> Regards.
> Ezequias
>
> Mark Cave-Ayland escreveu:
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
> bounces at postgis.refractions.net] On Behalf Of Johan V
> Sent: 14 July 2006 12:46
> To: postgis-users at postgis.refractions.net
> Subject: Re: [postgis-users] ERROR: Operation on two GEOMETRIES
> withdifferentSRIDs
>
> Hi Mark and others,
>
> Yes the geometry created on the fly is -1, but this same sql statement
> (with the on the fly created geometry) does work on another table with
> SRID=103300, but not on this one.
>
> I need this specific sql statement because it is a statement that is
> issued by a software product that makes a WFS/WMS service (GIS Service
> over the internet) of this table.
>
> So do I have to change or check some thing in the table to be able to
> use this kind of sql statement:
>
> SELECT the_geom FROM table_x WHERE ((the_geom && 'POLYGON((...))')).
>
> Greetz,
> Johan.
>
>
>
>
> Hi Johan,
>
> Hmmm that makes it a bit more tricky. Unfortunately the SQL being issued
> is
> relying on the implicit cast in PostgreSQL rather than using
> GeomFromText()/setSRID() as documented and so its behaviour may change
> across PostgreSQL/PostGIS versions - if you do get a chance to update the
> SQL in your client software, I would highly recommend it.
>
> As for the symptoms you are seeing, I am slightly baffled - can you post
> the
> output of \d <table> for one of your tables that works and your new one
> that
> doesn't, the exact SELECT queries being executed on each table, and can
> you
> confirm that both tables are in the same database running the same version
> of PostGIS?
>
>
> Kind regards,
>
> Mark.
>
> ------------------------
> WebBased Ltd
> 17 Research Way
> Plymouth
> PL6 8BT
>
> T: +44 (0)1752 797131
> F: +44 (0)1752 791023
>
> http://www.webbased.co.uk
> http://www.infomapper.com
> http://www.swtc.co.uk
>
> This email and any attachments are confidential to the intended recipient
> and may also be privileged. If you are not the intended recipient please
> delete it from your system and notify the sender. You should not copy it
> or
> use it for any purpose nor disclose or distribute its contents to any
> other
> person.
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
>
>
>
>
>
> --
> Ezequias Rodrigues da Rocha
> http://ezequiasrocha.blogspot.com
> msn:ezequias at hotmail.com
> "the worst of democracies is still better than the best of dictatorship"
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://lists.refractions.net/pipermail/postgis-users/attachments/20060714/a0e361da/attachment-0001.html
>
> ------------------------------
>
> Message: 25
> Date: Fri, 14 Jul 2006 15:54:44 -0300
> From: Eduardo Lu?s Garcia Escovar <eduardo.escovar at 3wt.com.br>
> Subject: Re: [postgis-users] Who is Using PostGIS? For What?
> To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
> Message-ID: <44B7E874.3060109 at 3wt.com.br>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> Hi Paul,
>
> Paul Ramsey escreveu:
> >
> > It is that time of year again (conference time) so I am going to ask my
> > annual question(s):
> >
> > Who are you?
> Eduardo Escovar, Computer Engineer working on a GIS Project for 3WT,
> Brazil.
> > What are you using PostGIS for?
> We are developing a GIS application in conjunction with the 38th
> Battalion of Military Police in São Carlos city. Basically, this GIS
> application shows a city map where it is possible to monitor police
> vehicles (equipped with GPS) and occurrences, as well as recommending
> directions to attend these occurrences.
> > Why did you choose PostGIS?
> Because it is free, works with Linux, and is well documented.
> > What makes you happy about your PostGIS installation?
> It is easy to use, and it works. :-)
> > What makes you unhappy about your PostGIS installation?
> It is hard to learn how to make a hard upgrade. Specially if you don't
> install things on default directories, or if you run PostgreSQL on a
> different port.
> But I've learned how to deal with it. ;-)
>
> Regards,
> Eduardo
>
>
> ------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> End of postgis-users Digest, Vol 45, Issue 14
> *********************************************
>
--
Regards,
Mark Thomas
spatialguru.net at gmail.com
205.529.9013
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20060717/b2655830/attachment.html>
More information about the postgis-users
mailing list