2

I am using PostgreSQL (9.5) and PostGIS (2.2.2).

I need to create a multipoint geometry while keeping track of the order of the points. (I would assume the point order to be the same order as the one used while creating the geometry).

geom = public.ST_GeomFromEWKT('SRID= 3067; MULTIPOINT((6703022 329690),(6702875 329189), (6706855 321939), (6706937 321896))')

Point 1: (6703022 329690) ; Point 2: (6702875 329189) ; Point 3: (6706855 321939) ; Point 4: (6706937 321896)

What would be the best approach?

Having one table with a geometry column or having two tables, one has all the other attributes and the other one has relations to the first table with the points and an id to give some order?

I also would like to know if it is possible to extract the n-th element of a multipoint (for instance the 3rd point).

For instance from the geom example above, I would like to get the Point 3... The return value should be (6706855 321939).

The same question applies for the first and last elements of the geometry. And by the way, is there a way to know how many points are in a multipoint geometry?

Asking how many points are in the geometry of the first example would return 4.

I have been trying to read the documentation about multipoints in the postgis reference but it seems quite confusing for me.

|improve this question
2

I would use the M dimension to store the order of the points.

|improve this answer
  • Interesting, and would you have a tip on where I could learn more about the M dimension? Can I still make spacial queries such as ST_DWithin (I mean, does the M dimension affects such queries? and if so, how could I drop the M dimension to have the query right?) I have started to read this : link – benoitespinola Jul 28 '16 at 15:08
  • The M coordinate is treated as an attribute and not as a spatial dimension. There are some passages about M coordinate in link – vagvaf Jul 28 '16 at 15:25
  • Wow, @vagvaf thank you so much for this! It is rather challenging to find documentation about those. I have found this page that states 'The same answer is returned, as ST_Length discards any coordinates in dimensions greater than 2. For this case, the function ST_Length3D is used to generate the correct answer.' Together with your clarification that M coordinate is treated as an attribute makes me go for your solution. Thanks a lot! :) – benoitespinola Jul 28 '16 at 15:40
  • glad to help you :) could u mark the question as solved? – vagvaf Jul 28 '16 at 16:14
  • M feels like reliable solution. I believe that the order of parts in multipoints can change in processing because in normal use cases the order does not matter at all. – user30184 Jul 28 '16 at 16:29
0

ST_NPoints will return the number of points in a MULTIPOINT.

SELECT ST_NPoints(ST_SetSRID(ST_GeomFromEWKT('MULTIPOINT(6703022 329690,6702875 329189,6706855 321939,6706937 321896)'),3067));

Result is: 4

ST_NumPoints will return the number of points in a LINESTRING.

SELECT ST_NumPoints(ST_LineFromMultiPoint(ST_SetSRID(ST_GeomFromEWKT('MULTIPOINT(6703022 329690,6702875 329189,6706855 321939,6706937 321896)'),3067)));

Result is: 4

ST_PointN will return the point you desire in a LINESTRING (below I asked for the second point).

SELECT ST_AsText(ST_PointN(ST_LineFromMultiPoint(ST_SetSRID(ST_GeomFromEWKT('MULTIPOINT(6703022 329690,6702875 329189,6706855 321939,6706937 321896)'),3067)),2));

Result is: POINT(6702875 329189)

|improve this answer
  • Hi, Thanks for your answer. Do you know how the point order is managed when inserting the data? Do you mean that I should use linestrings instead of multipoints to store the data? Thanks again for your answer. – benoitespinola Jul 29 '16 at 7:32
  • @benoitespinola I've updated the answer to fit the MULTIPOINT you described above. – kttii Jul 29 '16 at 13:14
  • Alright, thank you so much. I will try this as well. – benoitespinola Aug 1 '16 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.