Tag Info

Hot answers tagged

2

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this: SELECT A.* FROM osm_addr2 AS addr, osm_addr2 AS POI WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry); There's a relevant example in the manual (last two SQL examples), where a subquery is ...


2

Sure, using ST_Contains SELECT lines.* FROM lines, polygons WHERE ST_Contains(polygons.geom, lines.geom)


2

Look at creating a role (and possibly a user in that role) that only has SELECT privileges on a table, schema or group of tables. See the Postgres docs on creating roles. Once you have created a role, look at granting that role particular permissions. Available permissions are SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and so you want ...


1

From what you described, you don't need any extra common keys, as the geometries will take that role. So just create your query and join the tables spatially. Eg. something like this with corrected names and fields you want to select: SELECT congdist.name, congdist.representative, territories.info FROM congdist, territories WHERE ...


1

Apperantly, from your question I think you need to have some basic concept on database management(esp spatial database) and GIS softwares(QGIS) too.Both QGIS and PostgreSQL have comprehensive documentation you really need to consult with. To add @ John Barça, You can not connect postgresql to qgis rather reverse is done to solve the problem. If you need to ...


1

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)' LANGUAGE 'sql' IMMUTABLE; And so the query gets rewritten to use ...


1

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan. SELECT * FROM osm_addr2 AS addr, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...



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