The need is to retrieve the BOX extent of every group of data.
The gropu are on field ANNO.
The solution is a group by, an Union and the Extent.
So:
select ANNO, ST_Union(GEOMETRY) from the_schema.the_table group by ANNO order by ANNO;
it work.
So I complete composing the string I need.
-----
select
b.anno,
'EXTENT ' || ST_XMin(b.extent)::integer - 100 || ' ' || ST_YMin(b.extent)::integer - 100 || ' ' || ST_XMax(b.extent)::integer + 100 || ' ' || ST_YMax(b.extent)::integer + 100 as extent
from
(
select anno, ST_Extent (geometry) as extent from the_schema.the_table group by anno
) as b
order by
b.anno
;
-----
its all.
sabato 24 maggio 2014
giovedì 22 maggio 2014
Enumerate all the domains from a set of datasets
The need is to retrieve the list of all the used domains from all fields of a set of dataset.
Load all DXF on a DB using the GUI in a Spatialite DB
I choose this options:
mixed-mode
epsg::2832
use the unlinked-ring
force all to 2D.
after load all DXF
I have 4 tables.
prefix_line_layer_2d
prefix_point_layer_2d
prefix_polyg_layer_2d
prefix_text_layer_2d
I run this command:
SELECT CreateMetaCatalogTables(1);
After the command, I have a table named "splite_metacatalog "
It has all the descriptions of the tables of DB. It say if are PKsingle or composite, if are NULL, and so on...
Now I can launch the
UpdateMetaCatalogStatistics(transaction TRUE|FALSE, table, column)
To have the list for all the tables (exclude PK) this is the call:
SELECT UpdateMetaCatalogStatistics(1, table_name, column_name)
FROM splite_metacatalog;
after i run this to see all column names
SELECT DISTINCT column_name from splite_metacatalog order by column_name;
and I choose to accept only these columns: "layer" , "label", "rotation"
After I create the new table "freq" I run again the
drop table if exists splite_metacatalog_statistics;
drop table if exists splite_metacatalog;
SELECT CreateMetaCatalogTables(1);
SELECT UpdateMetaCatalogStatistics(1, 'freq', 'table_name', 'column_name');
now in the "splite_metacatalog_statistics" I have the list or distinct values in the columns choosed and theis numerosity.
now I export in dbf:
. dumpdbf splite_metacatalog_statistics stats.dbf CP1252
et-voila.
Load all DXF on a DB using the GUI in a Spatialite DB
I choose this options:
mixed-mode
epsg::2832
use the unlinked-ring
force all to 2D.
after load all DXF
I have 4 tables.
prefix_line_layer_2d
prefix_point_layer_2d
prefix_polyg_layer_2d
prefix_text_layer_2d
I run this command:
SELECT CreateMetaCatalogTables(1);
After the command, I have a table named "splite_metacatalog "
It has all the descriptions of the tables of DB. It say if are PKsingle or composite, if are NULL, and so on...
Now I can launch the
UpdateMetaCatalogStatistics(transaction TRUE|FALSE, table, column)
To have the list for all the tables (exclude PK) this is the call:
SELECT UpdateMetaCatalogStatistics(1, table_name, column_name)
FROM splite_metacatalog;
after i run this to see all column names
SELECT DISTINCT column_name from splite_metacatalog order by column_name;
and I choose to accept only these columns: "layer" , "label", "rotation"
After I create the new table "freq" I run again the
drop table if exists splite_metacatalog_statistics;
drop table if exists splite_metacatalog;
SELECT CreateMetaCatalogTables(1);
SELECT UpdateMetaCatalogStatistics(1, 'freq', 'table_name', 'column_name');
now in the "splite_metacatalog_statistics" I have the list or distinct values in the columns choosed and theis numerosity.
now I export in dbf:
. dumpdbf splite_metacatalog_statistics stats.dbf CP1252
et-voila.
martedì 20 maggio 2014
A font usable for cartography
One our need was to have a font open and free and narrow for our cartography.
We test some fonts.
A good choice could be the Liberation Font, but unfortunately the Narrow type has an ambigous not clear license.
So the choice was for the "Tuffy Font" that is a 100% public domain font.
The original home page is this:
http://tulrich.com/fonts/
Good.
We test some fonts.
A good choice could be the Liberation Font, but unfortunately the Narrow type has an ambigous not clear license.
So the choice was for the "Tuffy Font" that is a 100% public domain font.
The original home page is this:
http://tulrich.com/fonts/
Good.
domenica 18 maggio 2014
Find overlapped polygons
The goal is "how many polygons are overlapping other polygons in a dataset ?".
The solution is a simply spatial query:
The dataset is "dbcg_dgpv".
"pk_uid" is the primary key.
"geometry" is the geometric field (polygon).
----
select
a.pk_uid,
b.pk_uid
from
dbcg_dgpv as a inner join dbcg_dgpv as b on (a.pk_uid <> b.pk_uid)
where
ST_contains(b.geometry,a.geometry) = 1
AND
a.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'DB=main.dbcg_dgpv'
AND search_frame =b.geometry
);
----
The filter
..on (a.pk_uid <> b.pk_uid)
is to avoid to compare a polygon with itself
The solution is a simply spatial query:
The dataset is "dbcg_dgpv".
"pk_uid" is the primary key.
"geometry" is the geometric field (polygon).
----
select
a.pk_uid,
b.pk_uid
from
dbcg_dgpv as a inner join dbcg_dgpv as b on (a.pk_uid <> b.pk_uid)
where
ST_contains(b.geometry,a.geometry) = 1
AND
a.ROWID IN (
SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'DB=main.dbcg_dgpv'
AND search_frame =b.geometry
);
----
The filter
..on (a.pk_uid <> b.pk_uid)
is to avoid to compare a polygon with itself
lunedì 12 maggio 2014
TFW: Comma to Point
The TFW was build with the comma instead of point as decimal separator.
This is an issue for some GIS softwares so is need to resolve.
An example:
----
4,2336280797917594
8,7000389611034787e-005
-3,7967992801623495e-005
-4,2334471495687653
1687840,3554409891
4787158,6027319813
----
The solution is using the OSGEO4Wshell:
The sed command:
and a FOR cycle.
So:
suppose the tiff+tfw are all in this folder path
D:/temp/temp/prove
The for cycle is this:
for /R D:/temp/temp/prove %f IN (*.tfw) do sed "/[0-9]\,/s/,/\./g" -i %f < %f
Run and see that all the tfw are correctly with the point symbol.
like this:
---
4.2333743174393925
-2.3049048341267813e-005
-6.1198092636898077e-005
-4.2328667675685194
1577094.09993232
4940038.9227409437
---
ok, the problem is gone.
A.
This is an issue for some GIS softwares so is need to resolve.
An example:
----
4,2336280797917594
8,7000389611034787e-005
-3,7967992801623495e-005
-4,2334471495687653
1687840,3554409891
4787158,6027319813
----
The solution is using the OSGEO4Wshell:
The sed command:
sed "/[0-9]\,/s/,/\./g" -i file.tfw < file.tfw
and a FOR cycle.
So:
suppose the tiff+tfw are all in this folder path
D:/temp/temp/prove
The for cycle is this:
for /R D:/temp/temp/prove %f IN (*.tfw) do sed "/[0-9]\,/s/,/\./g" -i %f < %f
Run and see that all the tfw are correctly with the point symbol.
like this:
---
4.2333743174393925
-2.3049048341267813e-005
-6.1198092636898077e-005
-4.2328667675685194
1577094.09993232
4940038.9227409437
---
ok, the problem is gone.
A.
Iscriviti a:
Post (Atom)