3

From a table of point data, there are some groups of points that can define a line (cross sections of a water course). 'cross_section' is the name of the table with the point data, geometrypoint the column with the geometry. 'code_sequence_nr' is the column that specifies the point sequence, so it becomes a nice line. To make lines from groups of point data I have the following query:

CREATE TABLE cross_section_lines AS
SELECT st_makeline(geometrypoint) as cross_section,
        cross_section.profile_code,
        cross_section.administrative_area_id,
    FROM (SELECT geometrypoint, profile_code, administrative_area_id
        FROM cross_section
        ORDER BY code_sequence_nr) cross_section
    GROUP BY cross_section.profile_code, cross_section.administrative_area_id;

Now I need more information than the columns used for grouping, but if I add a column like this:

CREATE TABLE cross_section_lines AS
SELECT st_makeline(geometrypoint) as cross_section,
        cross_section.profile_code,
        cross_section.administrative_area_id,
        cross_section.profile_type_id
    FROM (SELECT geometrypoint, profile_code, administrative_area_id
        FROM cross_section
        ORDER BY code_sequence_nr) cross_section
    GROUP BY cross_section.profile_code, cross_section.administrative_area_id;

The following error occurs:

ProgrammingError: (psycopg2.errors.UndefinedColumn) column cross_section.profile_type_id does not exist

But that column is certainly there. Now how do I add the profile type (and other usefull columns) to the resulting table that is created?

2

Since you don't want the column in the GROUP BY section, you would have to aggregate the values of this column.

For instance, you can use STRING_AGG and possibly keep only the distinct values via the DISTINCT keyword. You may have to cast the column to text as well

SELECT id, string_agg(distinct colA::text, ',')
FROM mytable
GROUP BY id;
  • I've decided to solve my problem in a different way, using multiple lines that are noded. But this answer solves my question well. – Rob Rikken May 23 at 14:24
3

profile_type_id is not in your select statement so the outer select can't see it. You need to try something like:

CREATE TABLE cross_section_lines AS
    SELECT st_makeline(geometrypoint) as cross_section,
            cross_section.profile_code,
            cross_section.administrative_area_id,
            cross_section.profile_type_id
        FROM (SELECT geometrypoint, profile_code, administrative_area_id, profile_type_id
            FROM cross_section
            ORDER BY code_sequence_nr) cross_section
        GROUP BY cross_section.profile_code, cross_section.administrative_area_id, cross_section.profile_type_id;
  • Unfortunately that didn't do the trick: ProgrammingError: (psycopg2.errors.GroupingError) column "cross_section.profile_type_id" must appear in the GROUP BY clause or be used in an aggregate function – Rob Rikken May 23 at 9:57
  • That would work for the profile code, because that's the same for the whole profile. But there are other columns that are not the same for the whole profile. In that case the line would be split in however many groups there are of that item. – Rob Rikken May 23 at 10:06
  • 2
    In this case, you need to add to your question the INPUT - structure of your source table and an example of its content and OUTPUT what you would like to get... – Cyril May 23 at 10:11
  • That's probably true. This is my first question on stackexchange, so I was not really sure how much information people need to figure out a question :) . – Rob Rikken May 23 at 12:12

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.