I had two tables, one with polygons and one with points. The table of points had the attributes of value and time. I created a new table at postgis with this query

SELECT polygon.gid, time, avg(value) 
ON ST_Contains(polygon.geom,geo.geom)
GROUP BY polygon.gid,time

This created a table, where if the point is inside the polygon I have the average value at different times. The problem that I have is that, if a polygon does not contain any point then the avg(value) is null (which is something that i want to) but also creates a null at time attribute while I want the value of each time. This is for the empty polygons: enter image description here And this for the polygons with points inside: enter image description here How I can keep the time attribute for the empty polygons?

  • 1
    While you are using spatial functions, your root problem is with relational database operation and the SQL language, not GIS. As such, you would like be better off researching the in Database Administrators. I can't stress how important it is to stop referring to tables as shapefiles. They are not shapefiles. Describing the problem without stating the exact version of PostgreSQL in the body and referring to tables as shapefiles is not encouraging folks to help you. My suggestion from last time to name the tables usefully still stands, as well. – Vince Jun 23 '18 at 13:21
  • 1
    you are grouping by the polygon.gid; if there are no points per 'gid', there are no times either. what's your desired outcome exactly? – ThingumaBob Jun 23 '18 at 15:01
  • 2
    @Vince not "GIS" but definitely "Spatial", wouldn't you say? – DPSSpatial Jun 23 '18 at 16:35
  • 1
    No, the difficulty here is relational algebra, and a failure to understand the SQL language. The fact that the join constraint is spatial has no impact whatsoever on the solution. Maybe, if the question were framed for DBAs, it would contain table descriptions and a clearer description of the end result. – Vince Jun 23 '18 at 17:48
  • I think the greater scope of PostgreSQL/PostGIS is currently discussed in meta, albeit about tags. in this case it's hard to decide upon IMO: I would agree on having this question here, since it adresses the needs of a GIS related DB user, but Vince is of course right if this would be taken to court ,) as per some sources (I will come up with links...sry for now), this board is the de-facto knowledge base (after the docs, of course) when it comes to anything PostGIS related. – ThingumaBob Jun 24 '18 at 1:04

In point of fact, there isn't any time attribute for the features that fail to join and NULL is the correct way to capture this.

If you are unconcerned about corrupting the statistics of your data, you can use the SQL Coalesce function to change NULL values to a constant.

SELECT t.gid, Coalesce(j.time, 0) as time, avg(j.value) as avg_value
FROM polygon t
LEFT OUTER JOIN geo j ON ST_Contains(t.geom,j.geom) 
GROUP BY t.gid,j.time

Note the use of table aliases, explicitly referencing each column with its source table alias, and the naming of computed columns. These help to document the exact procees used in your code.

  • I couldn't find a way to do it so I loaded the data to python and did there! Thank you for your answer! +1 for giving an answer from different perspective! – Nathan Jun 25 '18 at 10:16

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.