makepolygon with geom info
|
View:
New views
9 Messages
—
Rating Filter:
Alert me
|
 |
makepolygon with geom info
by S.Reena
::
Rate this Message:
I have a table with foll fields( the_geom, name, latlon, ....)...i wanted to make a polygon by using those points whose name like 'abc' and put it in the map...
how to do dis? |
|
|
RE: makepolygon with geom info
by Regina Obe
::
Rate this Message:
Try
Are each of your the_geom single point geometries? If so I think you can do something of the form SELECT Max(f.gid) as newgid, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom, f.name FROM (SELECT gid, the_geom FROM foll ORDER BY name, gid) As f WHERE f.name = 'abc' GROUP BY f.name Although the above assumes that your gid field controls the order of the points along the line string and that your beginning and ending point are the same. If your beginning and ending are not the same - but your order is still right, then you can do SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid as newsort, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name If you leave the WHERE clause out, it will give you a Polygon record for each unique abc. Hope that helps, Regina -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of S.Reena Sent: Tuesday, July 01, 2008 7:33 AM To: postgis-users@... Subject: [postgis-users] makepolygon with geom info I have a table with foll fields( the_geom, name, latlon, ....)...i wanted to make a polygon by using those points whose name like 'abc' and put it in the map... how to do dis? -- View this message in context: http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18214128.htm l Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- 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. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
RE: makepolygon with geom info
by S.Reena
::
Rate this Message:
I tried that but its givin the error as f.gid doesnt exist....in the 2nd query..while the first query was fine ..it just said that lwshell doesnt exist..as the start n end points r different......
|
|
|
RE: makepolygon with geom info
by Paragon Corporation-2
::
Rate this Message:
Sorry I had a typo - try the below instead:
SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name Hope that helps, Regina -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of S.Reena Sent: Wednesday, July 02, 2008 1:42 AM To: postgis-users@... Subject: RE: [postgis-users] makepolygon with geom info I tried that but its givin the error as f.gid doesnt exist....in the 2nd query..while the first query was fine ..it just said that lwshell doesnt exist..as the start n end points r different...... -- View this message in context: http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18230652.html Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
Re: makepolygon with geom info
by S.Reena
::
Rate this Message:
Yupp that is done... now im trying to include that newgeom in DATA section of my map file to make a polygon ....bt it isnt wrking....this newgeom can be used for this or not....
|
|
|
RE: makepolygon with geom info
by Regina Obe
::
Rate this Message:
I've never tried doing that complicated of a query in Mapserver so not
sure it will fly. Well I did try once, but it was too slow so I just created a table out of the statement If you wanted to try the complex query DATA "newgeom FROM (SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name) As foo USING UNIQUE newid USING SRID=4326" (change 4326 with your SRID). Alternatively just create a new table SELECT * INTO follpoly FROM (SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name) As foo; ALTER TABLE follpoly ADD CONSTRAINT follpoly_pkey PRIMARY KEY(newgid); then your mapserver data part would just be DATA "newgeom FROM follpoly USING UNIQUE newid USING SRID=4326" You might also want to manually put in an entry in geometry_columns table since some GIS apps require it. Hope that helps, Regina -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of S.Reena Sent: Wednesday, July 02, 2008 9:39 AM To: postgis-users@... Subject: Re: [postgis-users] makepolygon with geom info Yupp that is done... now im trying to include that newgeom in DATA section of my map file to make a polygon ....bt it isnt wrking....this newgeom can be used for this or not.... S.Reena wrote: > > I have a table with foll fields( the_geom, name, latlon, ....)...i wanted > to make a polygon by using those points whose name like 'abc' and put it > in the map... > > how to do dis? > -- View this message in context: http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18237679.htm l Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- 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. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
RE: makepolygon with geom info
by Regina Obe
::
Rate this Message:
Typo again should be DATA "newgeom FROM (SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name) As foo USING UNIQUE newgid USING SRID=4326" (change 4326 with your SRID). -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of Obe, Regina Sent: Wednesday, July 02, 2008 11:55 AM To: PostGIS Users Discussion Subject: RE: [postgis-users] makepolygon with geom info I've never tried doing that complicated of a query in Mapserver so not sure it will fly. Well I did try once, but it was too slow so I just created a table out of the statement If you wanted to try the complex query DATA "newgeom FROM (SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name) As foo USING UNIQUE newid USING SRID=4326" (change 4326 with your SRID). Alternatively just create a new table SELECT * INTO follpoly FROM (SELECT Max(f.gid) as newgid, f.name, ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, 0 as psort, gid, the_geom FROM foll UNION ALL SELECT fs.* FROM (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom FROM foll ORDER BY name, gid) fs ORDER BY 1,2,3) As f WHERE f.name = 'abc' GROUP BY f.name) As foo; ALTER TABLE follpoly ADD CONSTRAINT follpoly_pkey PRIMARY KEY(newgid); then your mapserver data part would just be DATA "newgeom FROM follpoly USING UNIQUE newid USING SRID=4326" You might also want to manually put in an entry in geometry_columns table since some GIS apps require it. Hope that helps, Regina -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of S.Reena Sent: Wednesday, July 02, 2008 9:39 AM To: postgis-users@... Subject: Re: [postgis-users] makepolygon with geom info Yupp that is done... now im trying to include that newgeom in DATA section of my map file to make a polygon ....bt it isnt wrking....this newgeom can be used for this or not.... S.Reena wrote: > > I have a table with foll fields( the_geom, name, latlon, ....)...i wanted > to make a polygon by using those points whose name like 'abc' and put it > in the map... > > how to do dis? > -- View this message in context: http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18237679.htm l Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users ----------------------------------------- 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. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
RE: makepolygon with geom info
by S.Reena
::
Rate this Message:
Hi Regina,
I have made a new table with the query data as you suggested and added entry in geometry_columns ... I am able to see the polygon as postgis layer in Quantum GIS...but somehow its not getting displayed in map...Can you suggest something? Cheers, Reena.
|
|
|
RE: makepolygon with geom info
by Paragon Corporation-2
::
Rate this Message:
Reena,
Not quite sure what could be the problem. Hmm sorry about my comment off-list - I hadn't read this e-mail. Can you post a little bit of you Mapserver map. Could be the extent of your map is wrong, the projection doesn't match among a number of other problems. All ahrd to tell without seeing the map file. Probably worthwhile to post this to the UMN Mapserver mail list if you haven't already since it might be more of a mapserver question than PostGIS. Hope that helps, Regina -----Original Message----- From: postgis-users-bounces@... [mailto:postgis-users-bounces@...] On Behalf Of S.Reena Sent: Friday, July 25, 2008 3:52 AM To: postgis-users@... Subject: RE: [postgis-users] makepolygon with geom info Hi Regina, I have made a new table with the query data as you suggested and added entry in geometry_columns ... I am able to see the polygon as postgis layer in Quantum GIS...but somehow its not getting displayed in map...Can you suggest something? Cheers, Reena. Obe, Regina DND\MIS wrote: > > > Typo again should be > > > DATA "newgeom FROM (SELECT Max(f.gid) as newgid, f.name, > ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, > 0 as psort, gid, the_geom > FROM foll > UNION ALL > SELECT fs.* > FROM > (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom > FROM foll > ORDER BY name, gid) fs > ORDER BY 1,2,3) As f > WHERE f.name = 'abc' > GROUP BY f.name) As foo USING UNIQUE newgid USING SRID=4326" (change > 4326 with your SRID). > > -----Original Message----- > From: postgis-users-bounces@... > [mailto:postgis-users-bounces@...] On Behalf Of > Obe, Regina > Sent: Wednesday, July 02, 2008 11:55 AM > To: PostGIS Users Discussion > Subject: RE: [postgis-users] makepolygon with geom info > > I've never tried doing that complicated of a query in Mapserver so not > sure it will fly. Well I did try once, but it was too slow so I just > created a table out of the statement > > If you wanted to try the complex query > > > DATA "newgeom FROM (SELECT Max(f.gid) as newgid, f.name, > ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, > 0 as psort, gid, the_geom > FROM foll > UNION ALL > SELECT fs.* > FROM > (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom > FROM foll > ORDER BY name, gid) fs > ORDER BY 1,2,3) As f > WHERE f.name = 'abc' > GROUP BY f.name) As foo USING UNIQUE newid USING SRID=4326" (change > 4326 with your SRID). > > > > Alternatively just create a new table > > SELECT * INTO follpoly > FROM > (SELECT Max(f.gid) as newgid, f.name, > ST_MakePolygon(ST_MakeLine(f.the_geom)) As newgeom FROM (SELECT name, > 0 as psort, gid, the_geom > FROM foll > UNION ALL > SELECT fs.* > FROM > (SELECT DISTINCT ON(name) name, 1 As psort, gid , the_geom > FROM foll > ORDER BY name, gid) fs > ORDER BY 1,2,3) As f > WHERE f.name = 'abc' > GROUP BY f.name) As foo; > > ALTER TABLE follpoly > ADD CONSTRAINT follpoly_pkey PRIMARY KEY(newgid); > > then your mapserver data part would just be > > DATA "newgeom FROM follpoly USING UNIQUE newid USING SRID=4326" > > You might also want to manually put in an entry in geometry_columns > table since some GIS apps require it. > > > Hope that helps, > Regina > > > > > > -----Original Message----- > From: postgis-users-bounces@... > [mailto:postgis-users-bounces@...] On Behalf Of > S.Reena > Sent: Wednesday, July 02, 2008 9:39 AM > To: postgis-users@... > Subject: Re: [postgis-users] makepolygon with geom info > > > Yupp that is done... now im trying to include that newgeom in DATA > section of my map file to make a polygon ....bt it isnt wrking....this > newgeom can be used for this or not.... > > > > S.Reena wrote: >> >> I have a table with foll fields( the_geom, name, latlon, ....)...i > wanted >> to make a polygon by using those points whose name like 'abc' and put > it >> in the map... >> >> how to do dis? >> > > -- > View this message in context: > http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18237679.h > tm > l > Sent from the PostGIS - User mailing list archive at Nabble.com. > > _______________________________________________ > postgis-users mailing list > postgis-users@... > http://postgis.refractions.net/mailman/listinfo/postgis-users > ----------------------------------------- > 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. > > _______________________________________________ > postgis-users mailing list > postgis-users@... > http://postgis.refractions.net/mailman/listinfo/postgis-users > _______________________________________________ > postgis-users mailing list > postgis-users@... > http://postgis.refractions.net/mailman/listinfo/postgis-users > > -- View this message in context: http://www.nabble.com/makepolygon-with-geom-info-tp18214128p18646914.html Sent from the PostGIS - User mailing list archive at Nabble.com. _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
| Free embeddable forum powered by Nabble | Forum Help |
