Retrieve the extents for every category

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.

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.

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.

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

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:

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.