Tell me more ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.
up vote 1 down vote favorite

I have written a query that will create a table and perform a GROUP BY from an existing spatial table, but the table does have the geometry. I am wondering where, and what, in this query I put to make the new table have spatial geometry (points).

Thanks...

SELECT st_id, name, COUNT(st_id)
    INTO climate.test_stats
FROM climate.lugdc_stations
GROUP BY st_id, name
ORDER BY count DESC;

ALTER TABLE climate.test_stats
ADD COLUMN pk_id serial PRIMARY KEY;
share|improve this question
at the start of your query put CREATE TABLE <tablename> AS – Mapperz♦ Oct 29 '12 at 16:57
Thank you, but this is what I am asking. AS what? I am not sure the code to use to get the geometry from an existing table. – Ryan Garnett Oct 29 '12 at 17:00
I have added a few things to the end (based on a previous post) and it does make the geometry; however, when I try to load the geometry into QGIS it says it is an invalid layer and can not be loaded. When I looked at the source table there are four constraints, where the new table only has one. SELECT st_id, name, COUNT(st_id) INTO climate.nwont_stations FROM climate.lugdc_stations GROUP BY st_id, name ORDER BY count DESC; ALTER TABLE climate.nwont_stations ADD COLUMN pk_id serial PRIMARY KEY; SELECT Populate_Geometry_Columns('climate.nwont_stations'::regclass); – Ryan Garnett Oct 29 '12 at 17:13
What I am trying to do is a Group By on a specific column (st_id). Would I be better of to use the Aggregate function ST_Accum? If so, what would I write for my example? I have looked at the PostGIS manual and I am not sure what to do. – Ryan Garnett Oct 29 '12 at 22:13
I bet that in table lugdc_stations there is either a geometry or a geographic name. But anyway you have to give us the definition of all tables (test_stats, lugdc_stations), and especially also the one containing the geometries. – Stefan Nov 5 '12 at 20:07

1 Answer

up vote 5 down vote accepted 
 SELECT 
   st_id, name, 
   COUNT(st_id) AS count, 
   ST_Collect(geom) AS geom
 INTO climate.test_stats
 FROM climate.lugdc_stations
 GROUP BY st_id, name
 ORDER BY count DESC;
share|improve this answer
Thank you. I tried this and got the following: ERROR: column "geom" does not exist LINE 4: ST_Collect(geom) AS geom – Ryan Garnett Oct 29 '12 at 17:34
3  
I can't read the schema of your table magically, you'll have to fill the exact column names in yourself. – Paul Ramsey Oct 29 '12 at 19:45
Thank you Paul. This is essentially the reason for my question. All the how-to's I have seen have been very specific and I am not sure what is need and where to find them. – Ryan Garnett Oct 29 '12 at 19:51
@RyanGarnett Please consider accepting the answer if you problem is solved. Otherwise, please post the table schema. – underdark♦ Nov 5 '12 at 20:34
In some PostGIS tables, the geometry column is called 'the_geom'. In others, it is called 'geom'. You'll have to check your table structure to figure out which. – L_Holcombe Nov 5 '12 at 22:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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