[postgis-users] I need to select all featcher touch one polygon

Paul Ramsey pramsey at cleverelephant.ca
Thu Aug 7 15:39:47 PDT 2008 

I'm just reading into his interpretation of the word "touches". I
could be misconstruing, but most people don't mean the topologically
precise "touches" when they say "touches", they mean "not disjoint".

P

On Thu, Aug 7, 2008 at 3:12 PM,  <pcreso at pcreso.com> wrote:
> Hi Paul,
>
> Is there a reason you used
>  ST_Intersects(a.the_geom, b.the_geom)
> instead of
>  ST_Touches(a.the_geom, b.the_geom)
> in this case?
>
>
> Just wondering what I'm missing...
>
>   Brent Wood
>
>
>
>
> --- On Fri, 8/8/08, Paul Ramsey <pramsey at cleverelephant.ca> wrote:
>
>> From: Paul Ramsey <pramsey at cleverelephant.ca>
>> Subject: Re: [postgis-users] I need to select all featcher touch one polygon
>> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
>> Date: Friday, August 8, 2008, 7:45 AM
>> SELECT a.the_geom
>> FROM "IGFPLAN" a, "IGFPLAN" b
>> WHERE
>>   a."Id" = '2' AND
>>   b."Id" = '2' AND
>>   ST_Intersects(a.the_geom, b.the_geom)
>>
>> Leaving your columns in with case preserved really makes
>> writing SQL picky, IMO.
>>
>> P.
>>
>> On Thu, Aug 7, 2008 at 10:55 AM, eehab hamzeh
>> <eehab40 at hotmail.com> wrote:
>> >
>> > Hello every body,
>> >
>> > can any body help me to write sql statment that select
>> all the polygons that
>> > touch polygon in the same feature class.
>> > i try to do it in as below and find all polygon that
>> are within 100 m from
>> > the polygon 2.
>> >
>> > SELECT the_geom FROM
>> "public"."IGFPLAN"
>> > WHERE "Id" = '2' and
>> distance(the_geom, the_geom) < 100
>> >
>> >
>> > Thanks
>> >
>> > ihab
>> >
>> >
>> > ________________________________
>> >> From:
>> postgis-users-request at postgis.refractions.net
>> >> Subject: postgis-users Digest, Vol 70, Issue 6
>> >> To: postgis-users at postgis.refractions.net
>> >> Date: Wed, 6 Aug 2008 12:01:19 -0700
>> >>
>> >> 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. Newbie Question (Adam Vande More)
>> >> 2. Re: ST_Intersects (Paul Ramsey)
>> >> 3. Re: Newbie Question (Pedro Doria Meunier)
>> >> 4. Re: Newbie Question (Paul Ramsey)
>> >> 5. Re: Newbie Question (Pedro Doria Meunier)
>> >> 6. Update Error (Donald Ijams)
>> >> 7. RE: Update Error (Obe, Regina)
>> >> 8. Still cannot display postgis layers using ms4w
>> >> (kreshna_iceheart at yahoo.com)
>> >> 9. RE: Still cannot display postgis layers using
>> ms4w
>> >> (Paragon Corporation)
>> >> 10. RE: Still cannot display postgis layers using
>> ms4w< br>>
>> >> (kreshna_iceheart at yahoo.com)
>> >> 11. Configuration and getting started issues
>> (Khavindra Sivenandan)
>> >> 12. area calculation problem (danny)
>> >> 13. filing the holes in the swiss cheese?
>> (Burgholzer,Robert)
>> >> 14. Re: filing the holes in the swiss cheese?
>> (Paul Ramsey)
>> >>
>> >>
>> >>
>> ----------------------------------------------------------------------
>> >>
>> >> Message: 1
>> >> Date: Tue, 05 Aug 2008 14:24:44 -0500
>> >> From: Adam Vande More
>> <adam at imedmobility.com>
>> >> Subject: [postgis-users] Newbie Question
>> >> To: postgis-users at postgis.refractions.net
>> >> Message-ID:
>> <4898A8FC.40201 at imedmobility.com>
>> >> Content-Type: text/plain; charset=ISO-8859-1;
>> format=flowed
>> >>
>> >> Hello,
>> >>
>> >> Perhaps my questions were addressed earlier or in
>> some documentation I
>> >> missed. If so, I apologize in advance as I am
>> completely new to GIS and
>> >> PostGIS. Judging f rom the documentation I have
>> read, my needs are
>> >> extremely simple vs PostGIS capabilities however I
>> am unsure if the
>> >> functionality of what I want actually exists.
>> >>
>> >> I deal with areas in the US, territories that are
>> regional in nature but
>> >> not bounded by common things eg state lines, water
>> ways, counties, etc.
>> >> They are irregular shapes of varying land area. I
>> also have sets of
>> >> latitude and longitude points. I would like some
>> type of system where I
>> >> enter in a perimeter of territories, and find out
>> whether a particular
>> >> latitude and longitude point lies within that
>> perimeter.
>> >>
>> >> My understanding of this is that I would need to
>> either manually create
>> >> a GML type file with the perimeters in it, or use
>> some type of map
>> >> authoring software to accomplish this goal. Are my
>> understandings
>> >> correct and if so is there a recommendation for a
>> particular piece of
>> >> software which can fulfill this?
>> >>
>> >> Also, are the libraries/functions available for
>> determining whether a
>> >> point is in a perimeter?
>> >>
>> >> Much appreciated,
>> >>
>> >> --
>> >> Adam Vandemore
>> >> Systems Administrator
>> >> IMED Mobility
>> >> (605) 498-1610
>> >>
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 2
>> >> Date: Tue, 5 Aug 2008 12:39:30 -0700
>> >> From: "Paul Ramsey"
>> <pramsey at cleverelephant.ca>
>> >> Subject: Re: [postgis-users] ST_Intersects
>> >> To: pcreso at pcreso.com, "PostGIS Users
>> Discussion"
>> >> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <30fe546d0808051239v54067bcw6335074fe89d2fa6 at mail.gmail.com>
>> >> Content-Type: text/plain; charset=ISO-8859-1
>> >>
>> >> At core, this is not an indexing problem (of the
>> usual sort). When we
>> >> can fix the memory leaks on the preparedgeometry
>> code, and get it out
>> >> to users, it will make this case go much much
>> faster, but until then,
>> >> it's going to be slow.
>> >>
>> >> P.
>> >>
>> >> On Tue, Aug 5, 2008 at 11:47 AM,
>> <pcreso at pcreso.com> wrote:
>> >> > Hi Danny,
>> >> >
>> >> > As a maritime PostGIS user I have had issues
>> with large & complex
>> >> > polygons/multipoygons in PostGIS (&
>> GEOS), but not to the extent you seem
>> >> > to.
>> >> >
>> >> > I'm not an expert at this, but here are
>> some things you might try.
>> >> >
>> >> > It looks like it is not using the index on
>> sites, perhaps because
>> >> > postgres is applying a conversion to text for
>> your constant & the attribute.
>> >> > Can you try modifying your SQL to
>> >> >
>> >> > .... where a.sitecode =
>> 'xxxx'::<same spec as the sitecode column>
>> >> >
>> >> > (eg: where a.sitecode =
>> 'xxxx'::varchar(10) if sitecode is varchar(10) )
>> >> >
>> >> > To see if that enables the index to work
>> better.
>> >> >
>> >> > Alternatively, perhaps you could create a new
>> index on a
>> >> > (sitecode::text)
>> >> > Though I'm not sure if that's
>> possible....
>> >> >
>> >> >
>> >> > Where I have large complex polygons, I have
>> found tiling them really
>> >> > enables the spatial index to become useful. I
>> generate a grid in postgis,
>> >> > build a table of the intersections of the
>> grid & base layer & query against
>> >> > that.
>> >> >
>> >> > Essentially an index helps with long (deep)
>> tables, ie, lots of small
>> >> > records, much more than fat (broad) tables,
>> ie, few large records. Tiling
>> >> > geometries converts fat to deep, making
>> things much faster.
>> >> >
>> >> >
>> >> > Cheers,
>> >> >
>> >> > Brent Wood
>> >> >
>> >> >
>> >> > --- On Tue, 8/5/08, danny
>> <whatevar89 at gmail.com> wrote:
>> >> >
>> >> >> From: danny <whatevar89 at gmail.com>
>> >> >> Subject: Re: [postgis-users]
>> ST_Intersects
>> >> >> To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> >> Date: Tuesday, August 5, 2008, 11:14 PM
>> >> >> Guido, it's a bufferzone around the
>> contour of europe.
>> >> >>
>> >> >> On Tue, Aug 5, 2008 at 1:02 PM, Guido
>> Lemoine
>> >> >> <guido.lemoine at jrc.it> wrote:
>> >> >>
>> >> >> > Erik,
>> >> >> >
>> >> >> > I don't know why european_waters
>> would be a single
>> >> >> complex polygon?
>> >> >> > Do you mean it is a MULTIPOLYGON in
>> Postgis? If so, it
>> >> >> is easy to
>> >> >> > break it apart in single polygons.
>> If it includes
>> >> >> rivers, I can't imagine
>> >> >> > why it
>> >> >> > is a polygon in the first place. I
>> would expect a
>> >> >> LINESTRING (or
>> >> >> > MULTILINESTRING in your case).
>> >> >> >
>> >> >> > This explains, of course, w hy
>> performance is not much
>> >> >> improved. The &&
>> >> >> > operation does not really affect
>> that.
>> >> >> >
>> >> >> > GL
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > danny wrote:
>> >> >> >
>> >> >> >> Thanks Guido!
>> >> >> >>
>> >> >> >> The sample you gave performed
>> slightly better: 304
>> >> >> seconds. I'll still
>> >> >> >> have to wait a few months to let
>> it run on all my
>> >> >> data though. Yes, sitecode
>> >> >> >> has a "normal" index,
>> not a spatial one.
>> >> >> Both the_geom's have spatial ones.
>> >> >> >> The european_waters polygon is
>> one single complex
>> >> >> polygon, scale 1/100000.
>> >> >> >> What's annoying is that when
>> I do an intersect
>> >> >> in Arc View it's almost
>> >> >> >> instantaneou s. Of course I
>> don't get exaclty
>> >> >> what I wan't but with some
>> >> >> >> tweaking I can maybe get closer.
>> That would be a
>> >> >> great disappointment
>> >> >> >> though. I was really looking
>> forward to using
>> >> >> ST_Within intensively!
>> >> >> >>
>> >> >> >> Thanks Jean David, no
>> performance gain though by
>> >> >> using it in the WHERE
>> >> >> >> clause, where I intended to use
>> it in the first
>> >> >> place. I put it in the
>> >> >> >> Select for testing purposes.
>> >> >> >>
>> >> >> >> explain select sitecode from
>> sites a,
>> >> >> europe_waters b
>> >> >> >> where
>> st_intersects(a.the_geom,b.the_geom) and
>> >> >> a.sitecode = 'xxxx';
>> >> >> >>
>> >> >> >> "Nested Loop
>> (cost=0.00..461.71 rows=1
>> >> >> width=10)"
>> >> >> >&g t; " Join Filter:
>> _st_intersects(a.the_geom,
>> >> >> b.the_geom)"
>> >> >> >> " -> Seq Scan on sites a
>> >> >> (cost=0.00..453.43 rows=1
>> width=35764)"
>> >> >> >> " Filter: ((sitecode)::text
>> =
>> >> >> 'xxxx'::text)"
>> >> >> >> " -> Index Scan using
>> idx_europe_waters
>> >> >> on europe_waters b
>> >> >> >> (cost=0.00..8.27 rows=1
>> width=32)"
>> >> >> >> " Index Cond: (a.the_geom
>> &&
>> >> >> b.the_geom)"
>> >> >> >> " Filter: (a.the_geom
>> &&
>> >> >> b.the_geom)"
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> On Tue, Aug 5, 2008 at 10:53 AM,
>> Guido Lemoine
>> >> >> <guido.lemoine at jrc.it<mailto:
>> >> >> >> guido.lemoine at jrc.it>>
>> wrote:
>> >> >> >>
>> >> >> >> Erik
>> >> >&g t; >>
>> >> >> >> Try this, and see if it is any
>> faster:
>> >> >> >>
>> >> >> >> select
>> st_intersects(a.the_geom,b.the_geom)
>> >> >> from sites a,
>> >> >> >> europe_waters b where a.the_geom
>> &&
>> >> >> b.the_geom and
>> >> >> >> a.sitecode = 'xxxx';
>> >> >> >>
>> >> >> >> I guess you mean that sitecode
>> is indexed (not
>> >> >> spatially indexed),
>> >> >> >> because it seems to be a
>> varchar.
>> >> >> >> Both a and b should have a
>> spatial index on
>> >> >> the_geom, but I reckon
>> >> >> >> that is already the case. Also,
>> >> >> >> b (europe_layer) should not be a
>> single
>> >> >> polygon, but rather a set
>> >> >> >> of polygons.
>> >> >> >>
>> >> >> >> This is lesson 1 in the PostGIS
>> tutor ial, more
>> >> >> or less. In return
>> >> >> >> for my 2 minutes effort, you are
>> >> >> >> obliged to report the new
>> performance report,
>> >> >> so that future users
>> >> >> >> will benefit.
>> >> >> >> After all, I just saved you half
>> a year...
>> >> >> >>
>> >> >> >> GL
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> danny wrote:
>> >> >> >>
>> >> >> >> Hello,
>> >> >> >>
>> >> >> >> I'm wondering if it's
>> normal to
>> >> >> have a 420 second response
>> >> >> >> time for the following query.
>> >> >> >> A spatial index has been set on
>> the spatial
>> >> >> field (the_geom)
>> >> >> >> and other important fields (like
>> sitecode).
>> >> >> I've g enerously
>> >> >> >> tweaked the memory options for
>> postgresql.
>> >> >> >> With such a response time I
>> would have to
>> >> >> let my query run for
>> >> >> >> half a year before getting the
>> answer
>> >> >> I'm interested in! :)
>> >> >> >>
>> >> >> >> Anybody know how I can boost up
>> the process
>> >> >> or is it doomed to
>> >> >> >> always be so slow? The
>> europe_layer is
>> >> >> indeed a complex
>> >> >> >> polygon....
>> >> >> >>
>> >> >> >> select
>> >> >> st_intersects(a.the_geom,b.the_geom) from
>> sites a,
>> >> >> >> europe_waters b where a.sitecode
>> =
>> >> >> 'xxxx';
>> >> >> >>
>> >> >> >> "Nested Loop
>> (cost=0.00..496.18
>> >> >> rows=1310 width=35786)"
>> >> >> >> " -> Seq Scan on sites a
>> >> >> (cost=0.00..453.43 rows=1
>> >> >> >> width=35754)"
>> >> >> >> " Filter: ((sitecode)::text
>> =
>> >> >> 'xxxx'::text)"
>> >> >> >> " -> Seq Scan on
>> europe_waters b
>> >> >> (cost=0.00..23.10
>> >> >> >> rows=1310 width=32)"
>> >> >> >>
>> >> >> >> My true objective would be to
>> join thses
>> >> >> two tables through an
>> >> >> >> st_intersects but for the time
>> it is
>> >> >> unconceivable.
>> >> >> >> Many thanks,
>> >> >> >>
>> >> >> >> Erik
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> ------------------------------------------------------------------------
>> >> >> >>
>> >> >> >>
>> >> >> _________________________________________
>> ______
>> >> >> >> postgis-users mailing list
>> >> >> >>
>> postgis-users at postgis.refractions.net
>> >> >> >>
>> >> >>
>> <mailto:postgis-users at postgis.refractions.net>
>> >> >> >>
>> >> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >> >>
>> >> >> >>
>> >> >> >> --
>> >> >>
>> ----------------------------------------------------------------
>> >> >> >> Guido Lemoine
>> >> >> >> Joint Research Centre, European
>> Commission
>> >> >> >> Institute for the Protection and
>> Security of
>> >> >> the Citizen (IPSC)
>> >> >> >> Support to External Security
>> >> >> >> Via E. Fermi, 2749 TP 267 Ispra
>> 21027 (VA),
>> >> >> Italy
>> >> >> >> Tel. +39 0332 786239 (direct
>> line) Fax. +39
>> >> >> 0332 785154
>> >> >> >> WWW: http://ses.jrc.it
>> >> >> >>
>> >> >>
>> ----------------------------------------------------------------
>> >> >> >> Disclaimer:
>> >> >> >> Views expressed are those of the
>> individual and
>> >> >> do not represent
>> >> >> >> the views of the European
>> Commission
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> _______________________________________________
>> >> >> >> postgis-users mailing list
>> >> >> >>
>> postgis-users at postgis.refractions.net
>> >> >> >>
>> >> >>
>> <mailto:postgis-users at postgis.refractions.net>
>> >> >> >>
>> >> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> ------------------------------------------------------------------
>> >> >> ------
>> >> >> >>
>> >> >> >>
>> _______________________________________________
>> >> >> >> postgis-users mailing list
>> >> >> >>
>> postgis-users at postgis.refractions.net
>> >> >> >>
>> >> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> > --
>> >> >> >
>> >> >>
>> ----------------------------------------------------------------
>> >> >> > Guido Lemoine
>> >> >> > Joint Research Centre, European
>> Commission
>> >> >> > Institute for the Protection and
>> Security of the
>> >> >> Citizen (IPSC)
>> >> >> > Support to External Security
>> >> >> > Via E. Fermi, 2749 TP 267 Ispra
>> 21027 (VA), Italy
>> >> >> > Tel. +39 0332 786239 (direct line)
>> Fax. +39 0332
>> >> >> 785154
>> >> >> > WWW: http://s es.jrc.it
>> >> >> >
>> >> >>
>> ----------------------------------------------------------------
>> >> >> > Disclaimer:
>> >> >> > Views expressed are those of the
>> individual and do not
>> >> >> represent the views
>> >> >> > of the European Commission
>> >> >> >
>> >> >> >
>> >> >> >
>> _______________________________________________
>> >> >> > postgis-users mailing list
>> >> >> >
>> postgis-users at postgis.refractions.net
>> >> >> >
>> >> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >> >
>> >> >>
>> _______________________________________________
>> >> >> postgis-users mailing list
>> >> >> postgis-users at postgis.refractions.net
>> >> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >
>> _______________________________________________
>> >> > postgis-users m ailing list
>> >> > postgis-users at postgis.refractions.net
>> >> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 3
>> >> Date: Tue, 05 Aug 2008 20:45:01 +0100
>> >> From: Pedro Doria Meunier
>> <pdoria at netmadeira.com>
>> >> Subject: Re: [postgis-users] Newbie Question
>> >> To: PostGIS Users Discussion
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <1217965501.4389.8.camel at 213-190-214-90-cmts01.netmadeira.com>
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> Hi Adam,
>> >> This is what I use when I want to see which map to
>> use for a vehicle's
>> >> current position:
>> >>
>> >> SELECT myfield1 FROM maps AS m
>> >> WHERE m.b_box &&
>> geomfromtext('POINT(-16.3 32.7)',4326) LIMIT 1
>> >>
>> >> (Note: there might be a newer/better function in
>> PostGIS 1.3 ... )
>> >>
>> >> The top-level maps table consists of polygons
>> defining countries
>> >> boundaries.
>> >> This query returns the correspondent map for the
>> given location so this
>> >> should be applicable for your perimeters, which I
>> guess are polygons :)
>> >>
>> >> HTH,
>> >> Pedro Doria Meunier
>> >>
>> >>
>> >> On Tue, 2008-08-05 at 14:24 -0500, Adam Vande More
>> wrote:
>> >> > Hello,
>> >> >
>> >> > Perhaps my questions were addressed earlier
>> or in some documentation I
>> >> > missed. If so, I apologize in advance as I am
>> completely new to GIS and
>> >> > PostGIS. Judging from the documentation I
>> have read, my needs are
>> >> > extremely simple vs PostGIS capabilities
>> however I am unsure if the
>> >> > functionality of what I want actually exists.
>> >> >
>> >> > I deal with areas in the US, territories that
>> are regional in nature but
>> >> > not bounded by common things eg state lines,
>> water ways, countie s, etc.
>> >> > They are irregular shapes of varying land
>> area. I also have sets of
>> >> > latitude and longitude points. I would like
>> some type of system where I
>> >> > enter in a perimeter of territories, and find
>> out whether a particular
>> >> > latitude and longitude point lies within that
>> perimeter.
>> >> >
>> >> > My understanding of this is that I would need
>> to either manually create
>> >> > a GML type file with the perimeters in it, or
>> use some type of map
>> >> > authoring software to accomplish this goal.
>> Are my understandings
>> >> > correct and if so is there a recommendation
>> for a particular piece of
>> >> > software which can fulfill this?
>> >> >
>> >> > Also, are the libraries/functions available
>> for determining whether a
>> >> > point is in a perimeter?
>> >> >
>> >> > Much appreciated,
>> >> >
>> >> -------------- next part --------------
>> >> A non-tex t attachment was scrubbed...
>> >> Name: not available
>> >> Type: application/pgp-signature
>> >> Size: 189 bytes
>> >> Desc: This is a digitally signed message part
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080805/356f2fed/attachment-0001.bin
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 4
>> >> Date: Tue, 5 Aug 2008 12:46:14 -0700
>> >> From: "Paul Ramsey"
>> <pramsey at cleverelephant.ca>
>> >> Subject: Re: [postgis-users] Newbie Question
>> >> To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <30fe546d0808051246p6386bee5u2877020e9392a36d at mail.gmail.com>
>> >> Content-Type: text/plain; charset=ISO-8859-1
>> >>
>> >> Well, that's the opposite of the usual
>> point-in-polygon use case
>> >> (given an existing set of polygons and a point
>> input, tell me what
>> >> polygons it falls within), but it's equally
>> tractable.
>> > &gt ;
>> >> You need an input interface, and that will be the
>> hardest part of your
>> >> task. You can do something in Google Maps, if you
>> like, then stuff the
>> >> geometry down into a script (php, asp, whatever)
>> that in turn
>> >> inscribes it into a SQL statement ala:
>> >>
>> >> select points.id, points.name from points where
>> >> st_contains('POLYGON(....)',
>> points.geometry);
>> >>
>> >> Where the 'POLYGON(...)' is constructed by
>> your script from your user
>> >> interface input.
>> >>
>> >> Enjoy,
>> >>
>> >> P.
>> >>
>> >> On Tue, Aug 5, 2008 at 12:24 PM, Adam Vande More
>> <adam at imedmobility.com>
>> >> wrote:
>> >> > Hello,
>> >> >
>> >> > Perhaps my questions were addressed earlier
>> or in some documentation I
>> >> > missed. If so, I apologize in advance as I am
>> completely new to GIS and
>> >> > PostGIS. Judging from the documentation I
>> have read, my needs are
>> >> > extremely
>> >> > simple vs PostGIS capabilit ies however I am
>> unsure if the functionality
>> >> > of
>> >> > what I want actually exists.
>> >> >
>> >> > I deal with areas in the US, territories that
>> are regional in nature but
>> >> > not
>> >> > bounded by common things eg state lines,
>> water ways, counties, etc. They
>> >> > are irregular shapes of varying land area. I
>> also have sets of latitude
>> >> > and
>> >> > longitude points. I would like some type of
>> system where I enter in a
>> >> > perimeter of territories, and find out
>> whether a particular latitude and
>> >> > longitude point lies within that perimeter.
>> >> > My understanding of this is that I would need
>> to either manually create
>> >> > a
>> >> > GML type file with the perimeters in it, or
>> use some type of map
>> >> > authoring
>> >> > software to accomplish this goal. Are my
>> understandings correct and if
>> >> > so
>> >> > is there a recommendation for a particular
>> piece of software which can
>> >> > fulfill this?
>> >> >
>> > & gt; > Also, are the libraries/functions
>> available for determining whether
>> > a point
>> >> > is in a perimeter?
>> >> >
>> >> > Much appreciated,
>> >> >
>> >> > --
>> >> > Adam Vandemore
>> >> > Systems Administrator
>> >> > IMED Mobility
>> >> > (605) 498-1610
>> >> >
>> >> >
>> _______________________________________________
>> >> > postgis-users mailing list
>> >> > postgis-users at postgis.refractions.net
>> >> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 5
>> >> Date: Tue, 05 Aug 2008 20:57:39 +0100
>> >> From: Pedro Doria Meunier
>> <pdoria at netmadeira.com>
>> >> Subject: Re: [postgis-users] Newbie Question
>> >> To: PostGIS Users Discussion
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <1217966259.4389.12.camel at 213-190-214-90-cmts01.netmadeira.com&gt
>> ;
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> Shame on me... I should have read Adam's
>> question more attentively ...
>> >>
>> >> Adam:
>> >> Paul is absolutely right.
>> >>
>> >> Regards,
>> >> Pedro Doria Meunier
>> >>
>> >>
>> >> On Tue, 2008-08-05 at 12:46 -0700, Paul Ramsey
>> wrote:
>> >> > Well, that's the opposite of the usual
>> point-in-polygon use case
>> >> > (given an existing set of polygons and a
>> point input, tell me what
>> >> > polygons it falls within), but it's
>> equally tractable.
>> >> >
>> >> > You need an input interface, and that will be
>> the hardest part of your
>> >> > task. You can do something in Google Maps, if
>> you like, then stuff the
>> >> > geometry down into a script (php, asp,
>> whatever) that in turn
>> >> > inscribes it into a SQL statement ala:
>> >> >
>> >> > select points.id, points.name from points
>> where
>> >> > st_contains('POLYGON(....)', poi
>> nts.geometry);
>> >> >
>> >> > Where the 'POLYGON(...)' is
>> constructed by your script from your user
>> >> > interface input.
>> >> >
>> >> > Enjoy,
>> >> >
>> >> > P.
>> >> >
>> >> > On Tue, Aug 5, 2008 at 12:24 PM, Adam Vande
>> More <adam at imedmobility.com>
>> >> > wrote:
>> >> > > Hello,
>> >> > >
>> >> > > Perhaps my questions were addressed
>> earlier or in some documentation I
>> >> > > missed. If so, I apologize in advance as
>> I am completely new to GIS
>> >> > > and
>> >> > > PostGIS. Judging from the documentation
>> I have read, my needs are
>> >> > > extremely
>> >> > > simple vs PostGIS capabilities however I
>> am unsure if the
>> >> > > functionality of
>> >> > > what I want actually exists.
>> >> > >
>> >> > > I deal with areas in the US, territories
>> that are regional in nature
>> >> > > but not
>> >> > > bounded by common things eg state lines,
>> water ways, counties, etc.
>> >> > > They
>> >> > > are irregular shapes of varying land
>> area. I also have sets of
>> >> > > latitude and
>> >> > > longitude points. I would like some type
>> of system where I enter in a
>> >> > > perimeter of territories, and find out
>> whether a particular latitude
>> >> > > and
>> >> > > longitude point lies within that
>> perimeter.
>> >> > > My understanding of this is that I would
>> need to either manually
>> >> > > create a
>> >> > > GML type file with the perimeters in it,
>> or use some type of map
>> >> > > authoring
>> >> > > software to accomplish this goal. Are my
>> understandings correct and if
>> >> > > so
>> >> > > is there a recommendation for a
>> particular piece of software which can
>> >> > > fulfill this?
>> >> > >
>> >> > > Also, are the libraries/functions
>> available for determining whether a
>> >> > > point
>> >> > > is in a perimeter?
>> >> > >
>> >> > > Much appreciated,
>> >> > >
>> >> > > --
>> >> > > Adam Vandemore
>> >> > > Systems Administrator
>> >> > > IMED Mobility
>> >> > > (605) 498-1610
>> >> > >
>> >> > >
>> _______________________________________________
>> >> > > postgis-users mailing list
>> >> > > postgis-users at postgis.refractions.net
>> >> > >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> > >
>> >> >
>> _______________________________________________
>> >> > postgis-users mailing list
>> >> > postgis-users at postgis.refractions.net
>> >> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: not available
>> >> Type: application/pgp-signature
>> >> Size: 189 bytes
>> >> Desc: This is a digitally signed message part
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080805/98ad00f1/attachment-0001.bin
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 6
>> >> Date: Tue, 05 Aug 2008 15:00:35 -0700
>> >> From: Donald Ijams <dsijams at gmail.com>
>> >> Subject: [postgis-users] Update Error
>> >> To: postgis-users at postgis.refractions.net
>> >> Message-ID: <4898CD83.6070704 at gmail.com>
>> >> Content-Type: text/plain;
>> charset="iso-8859-1"
>> >>
>> >> When I attempt to update a geometry column from a
>> field in another table
>> >> with:
>> >>
>> >> UPDATE recent
>> >> SET xypoint = ST_GeomFromText((raw.x,
>> raw.y),102249)
>> >> from raw
>> >> where recent.casenum=raw.casenum;
>> >>
>> >> I get:
>> >>
>> >> *SQL error:*
>> >>
>> >> ERROR: function st_geomfromtext(record, integer)
>> does not exist
>> >> LINE 3: xypoint = ST_GeomFromText((raw.x,
>> raw.y),102249)
>> >> ^
>> >> HINT: No function matches the given name and
>> argument types. You might
>> >> need to add explicit type casts.
>> >>
>> >>
>> >> Probably something simple, but I'm not seeing
>> it.
>> >>
>> >> Don
>> >>
>> >> -------------- next part --------------
>> >> An HTML attachment was scrubbed...
>> >> URL:
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080805/7f4711de/attachment-0001.html
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 7
>> >> Date: Tue, 5 Aug 2008 22:59:56 -0400
>> >> From: "Obe, Regina"
>> <robe.dnd at cityofboston.gov>
>> >> Subject: RE: [postgis-users] Update Error
>> >> To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <53F9CF533E1AA14EA1F8C5C08ABC08D20197A16F at ZDND.DND.boston.cob>
>> >> Content-Type: text/plain;
>> charset="iso-8859-1"
>> >>
>> >>
>> >> ST_GeomFromText takes the form
>> ST_GeomFromText('POINT(' || raw.x || ' ' ||
>> >> raw.y || ')', 102249)
>> >>
>> >> Anyrate for point geometries its more efficient to
>> use ST_MakePoin t so
>> >> something like
>> >>
>> >>
>> >> UPDATE recent
>> >> SET xypoint = ST_SetSRID(ST_MakePoint(raw.x,
>> raw.y),102249)
>> >> from raw
>> >> where recent.casenum=raw.casenum;
>> >>
>> >> Hope that helps,
>> >> Regina
>> >>
>> >>
>> >> -----Original Message-----
>> >> From:
>> postgis-users-bounces at postgis.refractions.net on behalf of
>> Donald
>> >> Ijams
>> >> Sent: Tue 8/5/2008 6:00 PM
>> >> To: postgis-users at postgis.refractions.net
>> >> Subject: [postgis-users] Update Error
>> >>
>> >> When I attempt to update a geometry column from a
>> field in another table
>> >> with:
>> >>
>> >> UPDATE recent
>> >> SET xypoint = ST_GeomFromText((raw.x,
>> raw.y),102249)
>> >> from raw
>> >> where recent.casenum=raw.casenum;
>> >>
>> >> I get:
>> >>
>> >> *SQL error:*
>> >>
>> >> ERROR: function st_geomfromtext(record, integer)
>> does not exist
>> >> LINE 3: xypoint = ST_GeomFromText((raw.x,
>> raw.y),102249)
>> > &gt ; ^
>> >> HINT: No function matches the given name and
>> argument types. You might
>> >> need to add explicit type casts.
>> >>
>> >>
>> >> Probably something simple, but I'm not seeing
>> it.
>> >>
>> >> Don
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> -----------------------------------------
>> >> The substance of this message, including any
>> attachments, may be
>> >> confidential, legally privileged and/or exempt
>> from disclosure
>> >> pursuant to Massachusetts law. It is intended
>> >> solely for the addressee. If you received this in
>> error, please
>> >> contact the sender and delete the material from
>> any computer.
>> >> -------------- next part --------------
>> >> An HTML attachment was scrubbed...
>> >> URL:
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080805/61d5ef9c/attachment-0001.html
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 8
>> >> Date: Wed, 6 Aug 2008 00:17:05 -0700 (PDT)
>> >> From: "kreshna_iceheart at yahoo.com"
>> <kreshna_iceheart at yahoo.com>
>> >> Subject: [postgis-users] Still cannot display
>> postgis layers using
>> >> ms4w
>> >> To: postgis-users at postgis.refractions.net
>> >> Message-ID:
>> <295797.42389.qm at web56101.mail.re3.yahoo.com>
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> Help. Somebody please help. Anybody. :-(
>> >>
>> >> I am using postgreSQL 8.2.5 for Windows with
>> postGIS. The database was
>> >> installed using Windows .msi installation package.
>> I am also using ms4w
>> >> 2.2.7 that comes with MapServer 5.0.2. All
>> installed on Windows XP Service
>> >> Pack 2.
>> >>
>> >> The MapServer does support postGIS. When I
>> executed mapserv -v, I got the
>> >> following:
>> >> MapServer version 5.0.2 OUTPUT=GIF OUTPUT=PNG
>> OUTPUT=JPEG OUTPUT=WBMP
>> >> OUTPUT=PDF OUTPUT=SWF OUTPUT=SVG SUPPORTS=PROJ
>> SUPPORTS=AGG
>> >> SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER
>> SUPPORTS=WMS_CLIENT
>> >> SUPPORTS=WFS_SERVER SUPPOR TS=WFS_CLIENT
>> SUPPORTS=WCS_SERVER
>> >> SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI
>> SUPPORTS=THREADS SUPPORTS=GEOS
>> >> INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL
>> INPUT=SHAPEFILE
>> >>
>> >> In the postgreSQL database, I have created a
>> database named test_mapserv.
>> >> I also created a schema named rencana, and a table
>> named kawasanbencana. The
>> >> name of the geometry column is the_geom.
>> >> I have created a spatial index
>> (kawasanbencanaspix) on the geometry
>> >> column. I also use lowercase to write the
>> 'from' statement.
>> >>
>> >> The problem is: MapServer always gives the
>> "..Query error. Error executing
>> >> POSTGIS DECLARE.." error message, no matter
>> what I do. And I have tried
>> >> everything ad nauseam.
>> >>
>> >> I have tried specifying the DATA line without
>> USING UNIQUE ... USING SRID
>> >> line. I have tried specifying the DATA line with
>> USING UNIQUE ...USING SRID.
>> >> I have tried using double apostrophe (") and
>> single apostrophe ('). It
>> >> doesn't matter. Everything I have tried has
>> failed, a nd I still get the
>> >> error message no matter what I did.
>> >>
>> >> My mapfiles are attached in this mail. Each
>> reflect my futile attempt, and
>> >> all of them generated the error message.
>> >>
>> >>
>> >> My first attempt:
>> >> =================
>> >>
>> >> This is my first attempt. I have both the mapfile
>> and the error message
>> >> attached on this mail.
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from
>> rencana.kawasanbencana'
>> >>
>> >> And I got the following error message:
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID( 'BOX3D(528635
>> >> .525378392 9074209,609092.162121608
>> >>
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom')
>> ))'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version of
>> >> Jan 23/2004.
>> >>
>> >>
>> >> My second attempt:
>> >> ==================
>> >>
>> >> I tried adding USING UNIQUE to my DATA line. There
>> is something odd with
>> >> my spatial table though: whenever I view the data
>> contents with pgAdmin, I
>> >> can always see the oid column (first column to the
>> left, before the PK
>> >> column). However, I cannot find the oid column
>> when viewing the table's
>> >> column definition. The question is: does my table
>> have oid column or not?
>> >> And does it have something to do with the error?
>> >>
>> >> Nonethless, here's my lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique oid'
>> >>
>> >>
>> >> And I still get the same error message as above.
>> The difference is that
>> >> the error message now says 'oid' instead
>> of 'gid'. For instance,
>> >> "(the_geom)),'NDR'),gid" becomes
>> "(the_geom)),'NDR'),oid", but everything
>> >> else remains the same.
>> >>
>> >>
>> >> My third attempt:
>> >> =================
>> >>
>> >> Like I said in my second attempt, I'm not sure
>> whether my table has oid
>> >> column or not. Thus, in my third attempt I used
>> the gid column instead,
>> >> especially since the gid column is my primary key
>> column anyway. This is my
>> >> lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid'
>> >>
>> >> And I got exactly the same error message as my
>> first attempt.
>> >>
>> >>
>> >> My fourth attempt:
>> >> ==================
>> >>
>> >> In my fourth attempt, I used the gid column for my
>> unique id, and I am
>> >> using SRID=1. Here is the lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid using SRID=1'
>> >>
>> >> Well I still got the error message. This time, the
>> error message is
>> >> slightly different. Instead of getting
>> "::BOX3D,find_srid", I got
>> >> "::BOX3D,1".
>> >>
>> >> Here is the complete error message:
>> >> msDrawMap (): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608 9134520)'::BOX3D,1) )'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> >> Mappostgis.c - version of Jan 23/2004.
>> >>
>> >>
>> >> My fifth attempt:
>> >> =================
>> >>
>> >> Now I had been pretty much desperate, I just wrote
>> using SRID=#, closed my
>> >> eyes, and hoped for the best. Here's the
>> lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid using SRID=#'
>> >>
>> >> I still get an error message. This time, the error
>> message is different
>> >> altogether. Here's the message:
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'.
>> msPOSTGISLayerParseData(): Query error. Error parsing
>> >> POSTGIS data variable: You specified 'using
>> SRID=#' but didnt have any
>> >> numbers! More Help: Error with POSTGIS data
>> variable. You specified
>> >> 'the_geom from rencana.kawasanbencana using
>> unique gid using SRID=#'.
>> >> Standard ways of specifiying are : (1)
>> 'geometry_column from ge
>> >> ometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version of Jan
>> >> 23/2004.
>> >>
>> >>
>> >> I'm pretty much desperate. Could somebody
>> please tell what's wrong on my
>> >> mapfile? Or maybe ms4w just cannot display postGIS
>> data? Do I need to
>> >> install postGIS and MapServer on Linux, in order
>> to get them work together?
>> >> Please help. :-(
>> >>
>> >>
>> >> Thanks,
>> >> -Kresh
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: test_mapserv_01.map
>> >> Type: application/octet-stream
>> >> Size: 1005 bytes
>> >> Desc: not available
>> >> Url : http://lists.refractions.net/pipermail/pos
>> >>
>> tgis-users/attachments/20080806/ff428038/test_mapserv_01.dll
>> >> -------------- next part --------------
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608
>> >>
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom')
>> ))'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refract
>> >> ions.net/documentation/ Mappostgis.c - version of
>> Jan 23/2004.
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: test_mapserv_02.map
>> >> Type: application/octet-stream
>> >> Size: 1022 bytes
>> >> Desc: not available
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/ff428038/test_mapserv_02.bin
>> >> -------------- next part --------------
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392 9074209,609092.16212
>> 1608
>> >>
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom')
>> ))'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),oid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refract
>> >> ions.net/documentation/ Mappostgis.c - version of
>> Jan 23/2004.
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: test_mapserv_03.map
>> >> Type: application/octet-stream
>> >> Size: 1022 bytes
>> >> Desc: not available
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/ff4280
>> >> 38/test_mapserv_03.dll
>> >> -------------- next part --------------
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608
>> >>
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom')
>> ))'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique colu mn
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refract
>> >> ions.net/documentation/ Mappostgis.c - version of
>> Jan 23/2004.
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: test_mapserv_04.map
>> >> Type: application/octet-stream
>> >> Size: 1035 bytes
>> >> Desc: not available
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/ff428038/test_mapserv_04.bin
>> >> -------------- next part --------------
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608 9134520)'::BOX3D,1) )'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ ' More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version o
>> >> f Jan 23/2004.
>> >> -------------- next part --------------
>> >> A non-text attachment was scrubbed...
>> >> Name: test_mapserv_05.map
>> >> Type: application/octet-stream
>> >> Size: 1035 bytes
>> >> Desc: not available
>> >> Url :
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/ff428038/test_mapserv_05.dll
>> >> -------------- next part --------------
>> >> msDraw Map(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'.
>> msPOSTGISLayerParseData(): Query error. Error parsing
>> >> POSTGIS data variable: You specified 'using
>> SRID=#' but didnt have any
>> >> numbers! More Help: Error with POSTGIS data
>> variable. You specified
>> >> 'the_geom from rencana.kawasanbencana using
>> unique gid using SRID=#'.
>> >> Standard ways of specifiying are : (1)
>> 'geometry_column from geometry_table'
>> >> (2) 'geometry_column from (sub query) as foo
>> using unique column name using
>> >> SRID=srid#' Make sure you put in the
>> 'using unique column name' and 'using
>> >> SRID=#' clauses in. For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version of Jan
>> >> 23/2004.
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 9
>> >> Date: Wed, 6 Aug 2008 04:10:32 -0400
>> >> From: "Paragon Corporation"
>> <lr at pcorp.us>
>> >> Subject: RE: [postgis-users] Still cannot display
>> postgis layers using
>> >> ms4w
>> >> To: "'PostGIS Users Discussion'"
>> >> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> <001501c8f79b$e5fd1860$4d812e40 at l>
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> Just a guess. Get rid of your FILTER clause or
>> actually put a where
>> >> condition in your FILTER clause. I think the fact
>> that your FILTER clause
>> >> is empty is causing PostgreSQL to generate an
>> invalid query that looks
>> >> something like this
>> >>
>> >> SELECT *
>> >> FROM sometable
>> >> WHERE
>> >>
>> >> Can't have where without a where condition.
>> >>
>> >> Hope that helps,
>> >> Regina
>> >>
>> >>
>> >>
>> >> -----Original Message-----
>> >> From:
>> postgis-users-bounces at postgis.refractions.net
>> >>
>> [mailto:postgis-users-bounces at postgis.refractions.net] On
>> Behalf Of
>> >> kreshna_iceheart at yahoo.com
>> >> Sent: Wednesday, August 06, 2008 3:17 AM
>> >> To: postgis-users at postgis.refractions.net
>> >> Subject: [postgis-users] Still cannot display
>> postgis layers using ms4w
>> >>
>> >> Help. Somebody please help. Anybody. :-(
>> >>
>> >> I am using postgreSQL 8.2.5 for Windows with
>> postGIS. The database was
>> >> installed using Windows .msi installation package.
>> I am also using ms4w
>> >> 2.2.7 that comes with MapServer 5.0.2. All
>> installed on Windows XP Service
>> >> Pack 2.
>> >>
>> >> The MapServer does support postGIS. When I
>> executed mapserv -v, I got the
>> >> following:
>> >> MapServer version 5.0.2 OUTPUT=GIF OUTPUT=PNG
>> OUTPUT=JPEG OUTPUT=WBMP
>> >> OUTPUT=PDF OUTPUT=SWF OUTPUT=SVG SUPPORTS=PROJ
>> SUPPORTS=AGG
>> >> SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER
>> SUPPORTS=WMS_CLIENT
>> >> SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT
>> SUPPORTS=WCS_SERVER
>> >> SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI
>> SUPPORTS=THREADS SUPPORTS=GEOS
>> >> INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL
>> INPUT=SHAPEFILE
>> >>
>> >> In the postgreSQL database, I have created a dat
>> abase named test_mapserv.
>> >> I
>> >> also created a schema named rencana, and a table
>> named kawasanbencana. The
>> >> name of the geometry column is the_geom.
>> >> I have created a spatial index
>> (kawasanbencanaspix) on the geometry
>> >> column.
>> >> I also use lowercase to write the 'from'
>> statement.
>> >>
>> >> The problem is: MapServer always gives the
>> "..Query error. Error executing
>> >> POSTGIS DECLARE.." error message, no matter
>> what I do. And I have tried
>> >> everything ad nauseam.
>> >>
>> >> I have tried specifying the DATA line without
>> USING UNIQUE ... USING SRID
>> >> line. I have tried specifying the DATA line with
>> USING UNIQUE ...USING
>> >> SRID.
>> >> I have tried using double apostrophe (") and
>> single apostrophe ('). It
>> >> doesn't matter. Everything I have tried has
>> failed, and I still get the
>> >> error message no matter what I did.
>> >>
>> >> My mapfiles are attached in this mail. Each
>> reflect my futile attempt, and
>> >> a ll of them generated the error message.
>> >>
>> >>
>> >> My first attempt:
>> >> =================
>> >>
>> >> This is my first attempt. I have both the mapfile
>> and the error message
>> >> attached on this mail.
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from
>> rencana.kawasanbencana'
>> >>
>> >> And I got the following error message:
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608
>> >>
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanben
>> cana','the_geom') ))'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE
>> >> 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ '
>> >> More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique
>> >> column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version of
>> >> Jan
>> >> 23/2004.
>> >>
>> >>
>> >> My second attempt:
>> >> ==================
>> >>
>> >> I tried adding USING UNIQUE to my DATA line. There
>> is something odd with
>> >> my
>> >> spatial table though: whenever I view the data
>> contents with pgAdmin, I
>> >> can
>> >> always see the oid column (first column to the l
>> eft, before the PK
>> >> column).
>> >> However, I cannot find the oid column when viewing
>> the table's column
>> >> definition. The question is: does my table have
>> oid column or not? And
>> >> does
>> >> it have something to do with the error?
>> >>
>> >> Nonethless, here's my lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique oid'
>> >>
>> >>
>> >> And I still get the same error message as above.
>> The difference is that
>> >> the
>> >> error message now says 'oid' instead of
>> 'gid'. For instance,
>> >> "(the_geom)),'NDR'),gid" becomes
>> "(the_geom)),'NDR'),oid", but everything
>> >> else remains the same.
>> >>
>> >>
>> >> My third attempt:
>> >> =================
>> >>
>> >> Like I said in my second attempt, I'm not sure
>> whether my table has oid
>> >> column or not. Thus, in my third attemp t I used
>> the gid column instead,
>> >> especially since the gid column is my primary key
>> column anyway. This is
>> >> my
>> >> lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid'
>> >>
>> >> And I got exactly the same error message as my
>> first attempt.
>> >>
>> >>
>> >> My fourth attempt:
>> >> ==================
>> >>
>> >> In my fourth attempt, I used the gid column for my
>> unique id, and I am
>> >> using
>> >> SRID=1. Here is the lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid using
>> >> SRID=1'
>> >>
>> >> Well I still got the error message. This time, the
>> error message is
>> >> slightly
>> >> different. Instead of gett ing
>> "::BOX3D,find_srid", I got "::BOX3D,1".
>> >>
>> >> Here is the complete error message:
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'. prepare_database():
>> Query error. Error executing POSTGIS
>> >> DECLARE (the actual query) statement: 'DECLARE
>> mycursor BINARY CURSOR FOR
>> >> SELECT
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> from
>> >> rencana.kawasanbencana WHERE () and (the_geom
>> && setSRID(
>> >> 'BOX3D(528635.525378392
>> 9074209,609092.162121608 9134520)'::BOX3D,1) )'
>> >> Postgresql reports the error as 'ERROR: syntax
>> error at or near ")" LINE
>> >> 1:
>> >> ...DR'),gid::text from rencana.kawasanbencana
>> WHERE () and (the... ^ '
>> >> More
>> >> Help: Error with POSTGIS data variable. You
>> specified 'check your .map
>> >> file'. Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table' (2) 'geometry_column from
>> (sub query) as foo using unique
>> >> column name using SRID=srid#' Make sure you
>> put in the 'using unique
>> >> column
>> >> name' and 'using SRID=#' clauses in.
>> For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> >> Mappostgis.c - version of Jan 23/2004.
>> >>
>> >>
>> >> My fifth attempt:
>> >> =================
>> >>
>> >> Now I had been pretty much desperate, I just wrote
>> using SRID=#, closed my
>> >> eyes, and hoped for the best. Here's the
>> lines:
>> >> CONNECTIONTYPE postgis
>> >> CONNECTION 'host=localhost user=rtrw
>> password=123456 port=5432
>> >> dbname=test_mapserver'
>> >> DATA 'the_geom from rencana.kawasanbencana
>> using unique gid using
>> >> SRID=#'
>> >>
>> >> I still get an error message. This time, the error
>> message is different
>> >> altogether. Here's the message:
>> >> msDrawMap(): Image handling error. Failed to draw
>> layer named
>> >> 'kawasanbencana'.
>> msPOSTGISLayerParseData(): Query error. Error parsing
>> >> POSTGI S data variable: You specified 'using
>> SRID=#' but didnt have any
>> >> numbers! More Help: Error with POSTGIS data
>> variable. You specified
>> >> 'the_geom from rencana.kawasanbencana using
>> unique gid using SRID=#'.
>> >> Standard ways of specifiying are : (1)
>> 'geometry_column from
>> >> geometry_table'
>> >> (2) 'geometry_column from (sub query) as foo
>> using unique column name
>> >> using
>> >> SRID=srid#' Make sure you put in the
>> 'using unique column name' and 'using
>> >> SRID=#' clauses in. For more help, please see
>> >> http://postgis.refractions.net/documentation/
>> Mappostgis.c - version of
>> >> Jan
>> >> 23/2004.
>> >>
>> >>
>> >> I'm pretty much desperate. Could somebody
>> please tell what's wrong on my
>> >> mapfile? Or maybe ms4w just cannot display postGIS
>> data? Do I need to
>> >> install postGIS and MapServer on Linux, in order
>> to get them work
>> >> together?
>> >> Please help. :-(
>> >>
>> >>
>> >> Thanks,
>> >> -Kresh
>> >>
>> >>
>> >> < br>>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 10
>> >> Date: Wed, 6 Aug 2008 01:33:38 -0700 (PDT)
>> >> From: "kreshna_iceheart at yahoo.com"
>> <kreshna_iceheart at yahoo.com>
>> >> Subject: RE: [postgis-users] Still cannot display
>> postgis layers using
>> >> ms4w
>> >> To: PostGIS Users Discussion
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> <801791.19065.qm at web56103.mail.re3.yahoo.com>
>> >> Content-Type: text/plain; charset=us-ascii
>> >>
>> >> Dear Regina....
>> >>
>> >> YOU ARE CORRECT!!! I got rid of the empty FILTER
>> clause and now the map is
>> >> displayed properly! :-)
>> >>
>> >> Many many many (times infinity) thanks!
>> >> -Kresh
>> >>
>> >>
>> >> --- On Wed, 8/6/08, Paragon Corporation
>> <lr at pcorp.us> wrote:
>> >>
>> >> > From: Paragon Corporation
>> <lr at pcorp.us&g t;
>> >> > Subject: RE: [postgis-users] Still cannot
>> display postgis layers using
>> >> > ms4w
>> >> > To: "'PostGIS Users
>> Discussion'"
>> <postgis-users at postgis.refractions.net>
>> >> > Date: Wednesday, August 6, 2008, 4:10 AM
>> >> > Just a guess. Get rid of your FILTER clause
>> or actually put
>> >> > a where
>> >> > condition in your FILTER clause. I think the
>> fact that
>> >> > your FILTER clause
>> >> > is empty is causing PostgreSQL to generate an
>> invalid query
>> >> > that looks
>> >> > something like this
>> >> >
>> >> > SELECT *
>> >> > FROM sometable
>> >> > WHERE
>> >> >
>> >> > Can't have where without a where
>> condition.
>> >> >
>> >> > Hope that helps,
>> >> > Regina
>> >> >
>> >> >
>> >> >
>> >> > -----Original Message-----
>> >> > From:
>> postgis-users-bounces at postgis.refractions.net
>> >> > [mailto:postgis-users-bounces at postgis.refrac
>> tions.net] On
>> >> > Behalf Of
>> >> > kreshna_iceheart at yahoo.com
>> >> > Sent: Wednesday, August 06, 2008 3:17 AM
>> >> > To: postgis-users at postgis.refractions.net
>> >> > Subject: [postgis-users] Still cannot display
>> postgis
>> >> > layers using ms4w
>> >> >
>> >> > Help. Somebody please help. Anybody. :-(
>> >> >
>> >> > I am using postgreSQL 8.2.5 for Windows with
>> postGIS. The
>> >> > database was
>> >> > installed using Windows .msi installation
>> package. I am
>> >> > also using ms4w
>> >> > 2.2.7 that comes with MapServer 5.0.2. All
>> installed on
>> >> > Windows XP Service
>> >> > Pack 2.
>> >> >
>> >> > The MapServer does support postGIS. When I
>> executed mapserv
>> >> > -v, I got the
>> >> > following:
>> >> > MapServer version 5.0.2 OUTPUT=GIF OUTPUT=PNG
>> OUTPUT=JPEG
>> >> > OUTPUT=WBMP
>> >> > OUTPUT=PDF OUTPUT=SWF OUTPUT=SVG
>> SUPPORTS=PROJ SUPPORTS=AGG
>> >> > SUPPORTS=FREETYPE SUPPORTS=WMS_SERVER
>> SUPPORTS=WMS_CLIENT
>> >> > SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT
>> SUPPORTS=WCS_SERVER
>> >> > SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI
>> SUPPORTS=THREADS
>> >> > SUPPORTS=GEOS
>> >> > INPUT=JPEG INPUT=POSTGIS INPUT=OGR INPUT=GDAL
>> >> > INPUT=SHAPEFILE
>> >> >
>> >> > In the postgreSQL database, I have created a
>> database named
>> >> > test_mapserv. I
>> >> > also created a schema named rencana, and a
>> table named
>> >> > kawasanbencana. The
>> >> > name of the geometry column is the_geom.
>> >> > I have created a spatial index
>> (kawasanbencanaspix) on the
>> >> > geometry column.
>> >> > I also use lowercase to write the
>> 'from' statement.
>> >> >
>> >> > The problem is: MapServer always gives the
>> "..Query
>> >> > error. Error executing
>> >> > POSTGIS DECLARE.." error message, no
>> matter what I do.
>> >> > And I have tried
>> >> &g t; everything ad nauseam.
>> >> >
>> >> > I have tried specifying the DATA line without
>> USING UNIQUE
>> >> > ... USING SRID
>> >> > line. I have tried specifying the DATA line
>> with USING
>> >> > UNIQUE ...USING SRID.
>> >> > I have tried using double apostrophe (")
>> and single
>> >> > apostrophe ('). It
>> >> > doesn't matter. Everything I have tried
>> has failed, and
>> >> > I still get the
>> >> > error message no matter what I did.
>> >> >
>> >> > My mapfiles are attached in this mail. Each
>> reflect my
>> >> > futile attempt, and
>> >> > all of them generated the error message.
>> >> >
>> >> >
>> >> > My first attempt:
>> >> > =================
>> >> >
>> >> > This is my first attempt. I have both the
>> mapfile and the
>> >> > error message
>> >> > attached on this mail.
>> >> > CONNECTIONTYPE postgis
>> >> > CONNECTION 'host=localhost user=rtrw<
>> br>> > password=123456 port=5432
>> >> > dbname=test_mapserver'
>> >> > DATA 'the_geom from
>> rencana.kawasanbencana'
>> >> >
>> >> > And I got the following error message:
>> >> > msDrawMap(): Image handling error. Failed to
>> draw layer
>> >> > named
>> >> > 'kawasanbencana'. prepare_database():
>> Query error.
>> >> > Error executing POSTGIS
>> >> > DECLARE (the actual query) statement:
>> 'DECLARE mycursor
>> >> > BINARY CURSOR FOR
>> >> > SELECT
>> >> >
>> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
>> >> > from
>> >> > rencana.kawasanbencana WHERE () and (the_geom
>> &&
>> >> > setSRID(
>> >> > 'BOX3D(528635.525378392
>> 9074209,609092.162121608
>> >> >
>> 9134520)'::BOX3D,find_srid('','rencana.kawasanbencana','the_geom')
>> >> > ))'
>> >> > Postgresql reports the error as 'ERROR:
>> syntax error at
>> >> > or near ")" LINE 1:
>> >> > ...DR'),gid::text from
>> rencana.kawasanbencana WHERE ()
>> >> > and (the... ^ ' More
>> >> > Help: Error with POSTGIS data variable. You
>> specified
>> >> > 'check your .map
>> >> > file'. Standard ways of specifiying are :
>> (1)
>> >> > 'geometry_column from
>> >> > geometry_table' (2) 'geometry_column
>> from (sub
>> >> > query) as foo using unique
>> >> > column name using SRID=srid#' Make sure
>> you put in the
>> >> > 'using unique column
>> >> > name' and 'using SRID=#' clauses
>> in. For more
>> >> > help, please see
>> >> > http://postgis.refractions.net/documentation/
>> Mappostgis.c
>> >> > - version of Jan
>> >> > 23/2004.
>> >> >
>> >> >
>> >> > My second attempt:
>> >> > ==================
>> >> >
>> >> > I tried adding USING UNIQUE to my DATA line.
>> There is
>> >> > something odd with my
>> >> > spatial table though: whenever I view the
>> data contents
>> >> > with pgAdmin, I can> > always see the
>> oid column (first column to the
>> >> > left, before
>> >> > the PK column).
>> >> > However, I cannot find the oid column when
>> viewing the
>> >> > table's column
>> >> > definition. The question is: does my table
>> have oid column
>> >> > or not? And does
>> >> > it have something to do with the error?
>> >> >
>> >> > Nonethless, here's my lines:
>> >> > CONNECTIONTYPE postgis
>> >> > CONNECTION 'host=localhost user=rtrw
>> >> > password=123456 port=5432
>> >> > dbname=test_mapserver'
>> >> > DATA 'the_geom from
>> rencana.kawasanbencana using
>> >> > unique oid'
>> >> >
>> >> >
>> >> > And I still get the same error message as
>> above. The
>> >> > difference is that the
>> >> > error message now says 'oid' instead
>> of
>> >> > 'gid'. For instance,
>> >> > "(the_geom)),'NDR'),gid"
>> becomes
>> >> > "(the_geom)),'NDR'),oid",
>> but everything
>> >> > else remains the same.
>> >> >
>> >> >
>> >> > My third attempt:
>> >> > =================
>> >> >
>> >> > Like I said in my second attempt, I'm not
>> sure whether
>> >> > my table has oid
>> >> > column or not. Thus, in my third attempt I
>> used the gid
>> >> > column instead,
>> >> > especially since the gid column is my primary
>> key column
>> >> > anyway. This is my
>> >> > lines:
>> >> > CONNECTIONTYPE postgis
>> >> > CONNECTION 'host=localhost user=rtrw
>> >> > password=123456 port=5432
>> >> > dbname=test_mapserver'
>> >> > DATA 'the_geom from
>> rencana.kawasanbencana using
>> >> > unique gid'
>> >> >
>> >> > And I got exactly the same error message as
>> my first
>> >> > attempt.
>> >> >
>> >> >
>> >> > My fourth attempt:
>> >> > ==================
>> >> >
>> >> > In my fourth attempt, I used the gid column
>> for my un ique
>> >> > id, and I am using
>> >> > SRID=1. Here is the lines:
>> >> > CONNECTIONTYPE postgis
>> >> > CONNECTION 'host=localhost user=rtrw
>> >> > password=123456 port=5432
>> >> > dbname=test_mapserver'
>> >> > DATA 'the_geom from
>> rencana.kawasanbencana using
>> >> > unique gid using
>> >> > SRID=1'
>> >> >
>> >> > Well I still got the error message. This
>> time, the error
>> >> > message is slightly
>> >> > different. Instead of getting
>> >> > "::BOX3D,find_srid", I got
>> "::BOX3D,1".
>> >> >
>> >> > Here is the complete error message:
>> >> > msDrawMap(): Image handling error. Failed to
>> draw layer
>> >> > named
>> >> > 'kawasanbencana'. prepare_database():
>> Query error.
>> >> > Error executing POSTGIS
>> >> > DECLARE (the actual query) statement:
>> 'DECLARE mycursor
>> >> > BINARY CURSOR FOR
>> >> > SELECT
>> >> > asbinary(force_collection(fo
>> rce_2d(the_geom)),'NDR'),gid::text
>> >> > from
>> >> > rencana.kawasanbencana WHERE () and (the_geom
>> &&
>> >> > setSRID(
>> >> > 'BOX3D(528635.525378392
>> 9074209,609092.162121608
>> >> > 9134520)'::BOX3D,1) )'
>> >> > Postgresql reports the error as 'ERROR:
>> syntax error at
>> >> > or near ")" LINE 1:
>> >> > ...DR'),gid::text from
>> rencana.kawasanbencana WHERE ()
>> >> > and (the... ^ ' More
>> >> > Help: Error with POSTGIS data variable. You
>> specified
>> >> > 'check your .map
>> >> > file'. Standard ways of specifiying are :
>> (1)
>> >> > 'geometry_column from
>> >> > geometry_table' (2) 'geometry_column
>> from (sub
>> >> > query) as foo using unique
>> >> > column name using SRID=srid#' Make sure
>> you put in the
>> >> > 'using unique column
>> >> > name' and 'using SRID=#' clauses
>> in. For more
>> >> > help, please see
>> >> >
>> http://postgis.refractions.net/documentation/> >
>> Mappostgis.c - version
>> >> > of Jan 23/2004.
>> >> >
>> >> >
>> >> > My fifth attempt:
>> >> > =================
>> >> >
>> >> > Now I had been pretty much desperate, I just
>> wrote using
>> >> > SRID=#, closed my
>> >> > eyes, and hoped for the best. Here's the
>> lines:
>> >> > CONNECTIONTYPE postgis
>> >> > CONNECTION 'host=localhost user=rtrw
>> >> > password=123456 port=5432
>> >> > dbname=test_mapserver'
>> >> > DATA 'the_geom from
>> rencana.kawasanbencana using
>> >> > unique gid using
>> >> > SRID=#'
>> >> >
>> >> > I still get an error message. This time, the
>> error message
>> >> > is different
>> >> > altogether. Here's the message:
>> >> > msDrawMap(): Image handling error. Failed to
>> draw layer
>> >> > named
>> >> > 'kawasanbencana'.
>> msPOSTGISLayerParseData(): Query
>> >> > error. Error parsing
>> >> > POSTGIS data variable: You s pecified
>> 'using SRID=#'
>> >> > but didnt have any
>> >> > numbers! More Help: Error with POSTGIS data
>> variable. You
>> >> > specified
>> >> > 'the_geom from rencana.kawasanbencana
>> using unique gid
>> >> > using SRID=#'.
>> >> > Standard ways of specifiying are : (1)
>> 'geometry_column
>> >> > from geometry_table'
>> >> > (2) 'geometry_column from (sub query) as
>> foo using
>> >> > unique column name using
>> >> > SRID=srid#' Make sure you put in the
>> 'using unique
>> >> > column name' and 'using
>> >> > SRID=#' clauses in. For more help, please
>> see
>> >> > http://postgis.refractions.net/documentation/
>> Mappostgis.c
>> >> > - version of Jan
>> >> > 23/2004.
>> >> >
>> >> >
>> >> > I'm pretty much desperate. Could somebody
>> please tell
>> >> > what's wrong on my
>> >> > mapfile? Or maybe ms4w just cannot display
>> postGIS data? Do
>> >> > I need to
>> >> > install postGIS a nd MapServer on Linux, in
>> order to get
>> >> > them work together?
>> >> > Please help. :-(
>> >> >
>> >> >
>> >> > Thanks,
>> >> > -Kresh
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> _______________________________________________
>> >> > postgis-users mailing list
>> >> > postgis-users at postgis.refractions.net
>> >> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 11
>> >> Date: Wed, 6 Aug 2008 16:04:32 +0200
>> >> From: Khavindra Sivenandan
>> <khavindra at sefsa.co.za>
>> >> Subject: [postgis-users] Configuration and getting
>> started issues
>> >> To:
>> "postgis-users at postgis.refractions.net"
>> >> <postgis-users at postgis.refractions.n et>
>> >> Message-ID:
>> >>
>> <1677E63198782240825AEF9E248718DE764FB4613E at sefsaserver.sefsa.local>
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> Hi There
>> >>
>> >> I am new to PostGIS/PostgreSQL. Can somebody help
>> me with the following
>> >> support information.
>> >>
>> >> I went as far as installing PostgreSQL &
>> PostGIS. I require to configure
>> >> the Geometry_Columns Table as in the manual. It is
>> somewhat vague as to the
>> >> inputs into the columns F_Table_Catalogue,
>> F_Table_Schema & F_Table_Name. I
>> >> have managed to sort out the other columns as
>> indicated in the manual
>> >> (4.2.2)
>> >>
>> >> I have managed to connect to the database from
>> various opensource desktop
>> >> GIS software, but my database is not configured
>> correctly. Is there any
>> >> detailed material, other than the online manual I
>> can look up or can
>> >> somebody please help me to move on further from
>> here. Ultimately, I would
>> >> like to move data (shapefiles) into the database
>> within some classification
>> >> system (e.g. by Province, state, etc...) which can
>> then be utilised by
>> >> desktop and web users.
>> >>
>> >>
>> >> Thanks
>> >>
>> >> Khavin Sivenandan
>> >> Khavindra at sefsa.co.za
>> >> -------------- next part --------------
>> >> An HTML attachment was scrubbed...
>> >> URL:
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/34cc54ab/attachment-0001.html
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 12
>> >> Date: Wed, 6 Aug 2008 16:06:14 +0200
>> >> From: danny <whatevar89 at gmail.com>
>> >> Subject: [postgis-users] area calculation problem
>> >> To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <5c218180808060706k3bdd3a0ar5db0e09c6da759cb at mail.gmail.com>
>> >> Content-Type: text/plain;
>> charset="iso-8859-1"
>> >>
>> >> Hi, I have a problem.
>> >> I'm trying to calculate the area of a great
>> number of si tes on a specific
>> >> territory.
>> >> The problem is that these sites can overlap (as
>> they do very frequently)
>> >> and
>> >> I do not want to count the same portion of land
>> twice.
>> >> A st_union would work with this right?
>> Unfortunately my experience with
>> >> this
>> >> function is that it is way too slow for the
>> numerous polygons I would have
>> >> to merge. As a matter fact it would take years.
>> >> Does anybody know of a hack around this? (In
>> PostGIS or ArcGIS)
>> >>
>> >> Many thanks!
>> >> -------------- next part --------------
>> >> An HTML attachment was scrubbed...
>> >> URL:
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/3ce94bd4/attachment-0001.html
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 13
>> >> Date: Wed, 6 Aug 2008 11:19:19 -0400
>> >> From: "Burgholzer,Robert"
>> <rwburgholzer at deq.virginia.gov>
>> >> Subject: [postgis-users] filing the holes in the
>> swiss cheese?
>> > &g t; To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <6C097DA58429B743A67070F98BE73A370379B67B at deqex01.deq.local>
>> >> Content-Type: text/plain;
>> charset="us-ascii"
>> >>
>> >> I am trying to do a query of cities that are
>> contained by counties (in
>> >> Virginia, US), so that I can have a list that
>> cross-references by FIPS
>> >> these relationships.
>> >>
>> >>
>> >>
>> >> My query looks like this:
>> >>
>> >>
>> >>
>> >> select b.poli1 as fips, b.name, a.poli1 as
>> contained_by_fips, a.name
>> >>
>> >> from poli_bounds as a , poli_bounds as b
>> >>
>> >> where a.poli1 <> b.poli1 and a.the_geom
>> && b.the_geom
>> >>
>> >> and contains(convexhull(a.the_geom), b.the_geom)
>> >>
>> >> and a.projectid = 1
>> >>
>> >> and b.projectid = 1;
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> This works fairly well, but omits a handful of
>> cities that are most
>> >> definitely within the boundaries of the county.
>> >>
>> >>
>> >>
>> >> Is "convexhull()" the correct function
>> for this? Are my geometries
>> >> goofed up?
>> >>
>> >>
>> >>
>> >> Thanks!
>> >>
>> >>
>> >>
>> >> Robert W. Burgholzer
>> >>
>> >> Surface Water Modeler
>> >>
>> >> Office of Water Supply and Planning
>> >>
>> >> Virginia Department of Environmental Quality
>> >>
>> >> rwburgholzer at deq.virginia.gov
>> >>
>> >> 804-698-4405
>> >>
>> >> Open Source Modeling Tools:
>> >>
>> >> http://sourceforge.net/projects/npsource/
>> >>
>> >>
>> >>
>> >> -------------- next part --------------
>> >> An HTML attachment was scrubbed...
>> >> URL:
>> >>
>> http://lists.refractions.net/pipermail/postgis-users/attachments/20080806/1d85ac47/attachment-0001.html
>> >>
>> >> ------------------------------
>> >>
>> >> Message: 14
>> >> Date: Wed, 6 Aug 2008 11:15:43 -0700> From:
>> "Paul Ramsey"
>> >> <pramsey at cleverelephant.ca>
>> >> Subject: Re: [postgis-users] filing the holes in
>> the swiss cheese?
>> >> To: "PostGIS Users Discussion"
>> <postgis-users at postgis.refractions.net>
>> >> Message-ID:
>> >>
>> <30fe546d0808061115y273ba5b1xc178f9e3cd9f9c05 at mail.gmail.com>
>> >> Content-Type: text/plain; charset=ISO-8859-1
>> >>
>> >> try
>> >>
>> >> select b.poli1 as fips, b.name, a.poli1 as
>> contained_by_fips, a.name
>> >> from poli_bounds as a , poli_bounds as b
>> >> where a.poli1 <> b.poli1
>> >> and st_contains(a.the_geom,
>> st_pointonsurface(b.the_geom))
>> >> and a.projectid = 1
>> >> and b.projectid = 1;
>> >>
>> >> that should rid you of the boundary conditions
>> plaguing st_contains.
>> >>
>> >> P.
>> >>
>> >> ps - note the "modern" st_contains(),
>> with implicit index call.
>> >>
>> >> On Wed, Aug 6, 2008 at 8:19 AM, Burgholzer,Robert
>> >> <rwburgholzer at deq.virginia.gov> wrote:
>> >> > I am trying to do a query of cities that are
>> contained by counties (in
>> >> > Virginia, US), so that I can have a list that
>> cross-references by FIPS
>> >> > these
>> >> > relationships.
>> >> >
>> >> >
>> >> >
>> >> > My query looks like this:
>> >> >
>> >> >
>> >> >
>> >> > select b.poli1 as fips, b.name, a.poli1 as
>> contained_by_fips, a.name
>> >> >
>> >> > from poli_bounds as a , poli_bounds as b
>> >> >
>> >> > where a.poli1 <> b.poli1 and a.the_geom
>> && b.the_geom
>> >> >
>> >> > and contains(convexhull(a.the_geom),
>> b.the_geom)
>> >> >
>> >> > and a.projectid = 1
>> >> >
>> >> > and b.projectid = 1;
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >> > This works fairly well, but omits a handful
>> of cities that are most
>> >> > definitely within the boundaries of the
>> county.
>> >> >
>> >> >
>> >> >
>> > &g t; > Is "convexhull()" the correct
>> function for this? Are my geometries
>> > goofed
>> >> > up?
>> >> >
>> >> >
>> >> >
>> >> > Thanks!
>> >> >
>> >> >
>> >> >
>> >> > Robert W. Burgholzer
>> >> >
>> >> > Surface Water Modeler
>> >> >
>> >> > Office of Water Supply and Planning
>> >> >
>> >> > Virginia Department of Environmental Quality
>> >> >
>> >> > rwburgholzer at deq.virginia.gov
>> >> >
>> >> > 804-698-4405
>> >> >
>> >> > Open Source Modeling Tools:
>> >> >
>> >> > http://sourceforge.net/projects/npsource/
>> >> >
>> >> >
>> >> >
>> >> >
>> _______________________________________________
>> >> > postgis-users mailing list
>> >> > postgis-users at postgis.refractions.net
>> >> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >> >
>> >> >
>> >>
>> >>
>> >> ------------------------------
>> >> < br>>
>> _______________________________________________
>> >> postgis-users mailing list
>> >> postgis-users at postgis.refractions.net
>> >>
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >>
>> >>
>> >> End of postgis-users Digest, Vol 70, Issue 6
>> >> ********************************************
>> >
>> > ________________________________
>> > Connect to the next generation of MSN Messenger  Get
>> it now!
>> > _______________________________________________
>> > postgis-users mailing list
>> > postgis-users at postgis.refractions.net
>> >
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> >
>> >
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

More information about the postgis-users mailing list