Hot answers tagged

5

By using CTEs you are forcing the planner to evaluate each CTE component without looking at any other part of the query. Once you've extracted the results from the tables using the CTEs the fact that there is an index is lost to the planner. You've created a new, non-indexed relation in the CTE. In general, if you can write logic as a plain join, always do ...


2

First thing you need to do is to set your date field correctly as otherwise you canĀ“t order it. Something like this(refer to the documentation for the correct time format): alter column time TYPE timestamp USING time::timestamp without time zone Or if you want to have directly in your query without changing the column you can use the CAST function. Then ...


1

You can execute SQL queries and load results as layers in QGIS using the DB Manager. If you still want to go through csv you must take care that WKT-geometry is included in the csv. You can make the query and save the result to CSV with ogr2ogr using the following command: ogr2ogr -f CSV -lco GEOMETRY=AS_WKT -sql "select avi, st_union(geom) geom from ...


1

You could use a subquery and ST_Union select ST_AsTexT(ST_Union(a.singlegeom, b.singlegeom )) from ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM PUBLIC .mzones WHERE ID = ALL (ARRAY [ 'GMZ730' ]) ) a, ( SELECT ST_COLLECT (ST_Simplify(geom ,. 005)) AS singlegeom FROM counties WHERE fips IN ...


1

Not sure what do you mean with "a house next to it" but here an example of what you can try: SELECT a.id, a.geometry, 'T'::text as type FROM houses a, houses b WHERE ST_Intersects(a.geometry,b.geometry) AND a.id != b.id Could be done with other spatial operator (ST_DWithin could be a better candidate). Better with Gist index on geometry field.


1

PostgreSQL partitioning is only going to make things faster if you have a non-spatial constraint in your query and that constraint is applied to each child table in the partition via constraint exclusion. (The constraint exclusion config also has to be turned on.) Partitioning will be useful for performance, if you have a key you can build constrained ...



Only top voted, non community-wiki answers of a minimum length are eligible