[postgis-users] I need to select all featcher touch one polygon
eehab hamzeh
eehab40 at hotmail.com
Thu Aug 7 10:55:38 PDT 2008
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
> (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 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
>
>
>
> ------------------------------
>
> 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, why 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
> >> >> instantaneous. 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)"
> >> >> " 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
> >> >>
> >> >> 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 tutorial, 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 generously
> >> >> 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://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
> >> >
> >> 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 mailing 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, 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,
> >
> -------------- 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/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.
>
> 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 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
> >
>
>
> ------------------------------
>
> 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>
> 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(....)', 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 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_MakePoint 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)
> ^
> 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 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 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 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 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
>
>
>
>
>
>
>
>
>
>
>
> -------------- 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/postgis-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.162121608 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/ff428038/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 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_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 --------------
> 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 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 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, 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 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 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> ------------------------------
>
> 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>
> > 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.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 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, 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
> > 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 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
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > _______________________________________________
> > 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.net>
> 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 sites 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?
> 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.
> >
> >
> >
> > 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
> >
> >
>
>
> ------------------------------
>
> _______________________________________________
> 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
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmailtagline
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080807/5c4f438e/attachment.html>
More information about the postgis-users
mailing list