New answers tagged sql
8
SpatiaLite requires you to explicitly register geometry columns in its ecosystem (updating metadata, adding triggers) using the provided management functions; SQLite does not support automatic detection like with PostgreSQLs type-modifier system.
Having created a new table as you specified you should be able to 'spatially enable' it with
SELECT ...
0
First of all, the ST_Collect function expects a geometry column.
To do this, you must use a subquery.
Here is an example:
SELECT ST_Collect(tmp.geometry)
FROM (SELECT geometry FROM geo_table_wgs84 WHERE file_name = 'MyFileName') as tmp
2
If you want to run a query within a function call, it must be enclosed in another set of parentheses select myfunction((select .. from ..))
But this won't be necessary for this query. ST_Collect expects a geometry field, not a table and even less record(s) with something else but geometries.
INSERT INTO multypoly_test (name,geom)
SELECT 'MyName', ST_Collect(...
0
Credits to @Gabriel De Luca from his answer
https://gis.stackexchange.com/a/364575/99589
In the Field Calculator use the following expression array_find(array_agg($id,"group"),$id)+1, where "group" is an attribute that will restart the count when a new group appears.
0
You can also do this with QGIS 3 field calculator more easy:
array_find(array_agg("GROUPFIELDNAME"||'_'||-$id,"GROUPFIELDNAME"),"GROUPFIELDNAME"||'_'||-$id)+1
Replace "GROUPFIELDNAME" with the name of your field you want to group by.
0
The query below creates a linestring from the multilinestring provied.
select st_astext(st_makeline(geom))
from ST_Dump(ST_GeomFromText('MULTILINESTRING((-3.16420835153456 55.9269166007097,-3.164222 55.926918),(-3.1642070167833 55.9269296196706,-3.16421351659546 55.9268662214904),
(-3.16421351659546 55.9268662214904,-3.16421636372824 55.9268384509897),(-3....
0
Though not specifically GIS related, I'm working on something similar, so here's a quick solution.
Assuming you're using psycopg2 and already have a connection and a cursor set up to your database:
#array of table names
tables = ['schools_current', 'schools_projected']
for x in tables:
sql_statement = 'select * from dpsdata.' + x + ';'
#...
Top 50 recent answers are included
