points to polygon

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

points to polygon

qxzhou
Hello everyone,

Is there an easy way to make a polygon with 4 points (startpoint, endpoint, start_proj, end_proj in the below table) for each record?



Best,
Qingxiao
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

ebocher
Administrator
Hello,

Use select POLYGON((75.15 29.53 1,77 29 1,77.6 29.5 2,75.15 29.53 2)) :: geometry; and you will have a new table with one polygon.

Have fun with orbisgis.

Erwan


2013/5/6 qxzhou [via OrbisGIS] <[hidden email]>
Hello everyone,

Is there an easy way to make a polygon with 4 points (startpoint, endpoint, start_proj, end_proj in the below table) for each record?



Best,
Qingxiao


If you reply to this email, your message will be added to the discussion below:
http://orbisgis.3871844.n2.nabble.com/points-to-polygon-tp7574836.html
To start a new topic under OrbisGIS Users, email [hidden email]
To unsubscribe from OrbisGIS Users, click here.
NAML



--
Institut de recherche en sciences et techniques de la ville
École Centrale de Nantes
BP 92101
1 rue de la Noë, 44321 NANTES Cedex 3
France
Tél : 02 40 37 68 67
http://www.irstv.fr/
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

qxzhou
Morning Erwan,

I tried the query "select POLYGON((75.15 29.53 1,77 29 1,77.6 29.5 2,75.15 29.53 2)) :: geometry;" but it didn't work neither in version 3 nor in version 4...

I used "St_GeomUnionArg"  to make two columns of points together, and then again , so I made a union of the 4 columns of points.
At last, with the function "st_convexhull", I made a polygon with the union of 4 points...

It works but too complicated I think...

Qingxiao
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

Gwendall Petit
Administrator
The correct syntax is
select 'POLYGON((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0))' :: geometry;
But for your problem I'm not that is the solution...

Can you send us an example of your layer (juste few lines) to make tests ?

Regards

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

qxzhou
Hi Gwendall,

Happy to see you again!
Here is a few lines of my data: test.gdms
I'm now doing a real project of Saclay, application of URBS on this area. Orbisgis is still a tool I use frequently at this time.
But sometimes the mixed usage of version 3 and version 4 makes me confused... For example, "select...union select..." is only valid in version 4 and "ToString() " is only valid in version 3...
Otherwise, Orbisgis is still a tool I prefer because of the facility to write SQL squery, the large amount of functions already writen and the possibility to create a new plug-in...

Yours,
Qingxiao

Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

Gwendall Petit
Administrator
Thanks for your feedback.

I tried to resolve your problem ... and it's not so easy.

Below is something that is not perfect but that is working well
-- all points are listed in the same table, that contains two fields : the_geom and the id
CREATE TABLE g1 AS SELECT startpoint as the_geom, new_id FROM test UNION SELECT endpoint as the_geom, new_id FROM test;
CREATE TABLE g2 AS SELECT start_proj as the_geom, new_id FROM test UNION SELECT end_proj as the_geom, new_id FROM test;
CREATE TABLE g AS SELECT * FROM g1 UNION SELECT * FROM g2;

-- Points are unified if they are linked to the same object
CREATE TABLE points AS SELECT ST_UNION(the_geom) as the_geom, new_id FROM g GROUP BY new_id;
-- We produce the polygons on the basis of each multipoints
CREATE TABLE polygons AS SELECT ST_CONVEXHULL(the_geom) as the_geom, new_id FROM points;

DROP TABLE g1 PURGE;
DROP TABLE g2 PURGE;
DROP TABLE g PURGE;
DROP TABLE points PURGE;
And here is the result :




Between V3.0.2 and V4.0 the SQL langage as changed (in an optimal way ... to be more compliant with the standard). That's why some things have been modified.

Concerning the UNION :
In V3.0.2, you have to use:
CREATE TABLE toto AS SELECT table1 UNION table2;
In V4.0, you have to use :
CREATE TABLE toto AS SELECT *  FROM table1 UNION SELECT *  FROM table2;
Concerning the conversion (cast) of fields :

In the V3.0.2 we had some specific functions (like ToString). But now in the V4.0 we use a magic term that allows to convert on the fly. This magic term is ::
So you can now write
SELECT *, my_field_int :: string as my_field_string FROM my_table;
Hope it helps.

Gwendall
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

qxzhou

yes, it works. Thank you very much.
The conversion term :: is very interesting.

Qingxiao
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

qxzhou
In reply to this post by Gwendall Petit

and where can we find the new features of version 4, especailly for the SQL function and query ?

Qingxiao
Reply | Threaded
Open this post in threaded view
|

Re: points to polygon

Gwendall Petit
Administrator
The documentation is currently under construction (lot of work).
I hope we will have something serious in few weeks

Regards

Gwendall