SHPRead
Signatures
SHPRead(VARCHAR path);
SHPRead(VARCHAR path, BOOLEAN deleteTable);
SHPRead(VARCHAR path, VARCHAR tableName);
SHPRead(VARCHAR path, VARCHAR tableName, BOOLEAN deleteTable);
SHPRead(VARCHAR path, VARCHAR tableName, VARCHAR fileEncoding);
SHPRead(VARCHAR path, VARCHAR tableName, VARCHAR fileEncoding, BOOLEAN deleteTable);
Description
Reads the file specified by path as a shapefile and copies its contents into a new table tableName in the database.
A new column named PK, storing a primary key (INT value), is added. If the input .shp has already a PK column then the added column is named PK2 (and so on).
Define fileEncoding to force encoding (useful when the header is missing encoding information) (default value is ISO-8859-1).
If:
the
tableNameparameter is not specified, then the resulting table has the same name as the shapefile.the
deleteTableparameter istrueand tabletableNamealready exists in the database, then tabletableNamewill be removed / replaced by the new one. Else (nodeleteTableparameter ordeleteTableequal tofalse), an error indicating that the tabletableNamealready exists will be throwned.
Warning on the input file name
When a tablename is not specified, special caracters in the input file name are not allowed. The possible caracters are as follow: A to Z, _ and 0 to 9.
Examples
In following example, we have a SHP file, which is stored here : /home/user/city.shp. This file is structured as follow.
THE_GEOM |
NAME |
ID |
|---|---|---|
MULTIPOLYGON(((…))) |
Vannes |
56260 |
MULTIPOLYGON(((…))) |
Theix |
56251 |
MULTIPOLYGON(((…))) |
Bréhan |
56024 |
1. Case with path
CALL SHPRead('/home/user/city.shp');
The table CITY is created and a new PK column is added:
PK |
THE_GEOM |
NAME |
ID |
|---|---|---|---|
1 |
MULTIPOLYGON(((…))) |
Vannes |
56260 |
… |
… |
… |
… |
2. Case with path and tableName
CALL SHPRead('/home/user/city.shp', 'MyCity');
The table MYCITY is created.
3. Case with fileEncoding
In the next two examples, we show what happens when we attempt to read a SHP file with the wrong encoding, and how to fix it.
Here UTF-8 doesn’t understand accented characters, so the city named Bréhan is displayed as Br.
CALL SHPRead('/home/user/city.shp', 'CITYutf', 'utf-8');
SELECT * FROM CITYutf;
Answer:
PK |
THE_GEOM |
NAME |
ID |
|---|---|---|---|
1 |
MULTIPOLYGON(((…))) |
Vannes |
56260 |
2 |
MULTIPOLYGON(((…))) |
Theix |
56251 |
3 |
MULTIPOLYGON(((…))) |
Br |
56024 |
To fix this problem, we specify the right encoding (iso-8859-1):
CALL SHPRead('/home/user/city.shp', 'CITYiso', 'iso-8859-1');
SELECT * FROM CITYiso;
Answer:
PK |
THE_GEOM |
NAME |
ID |
|---|---|---|---|
1 |
MULTIPOLYGON(((…))) |
Vannes |
56260 |
2 |
MULTIPOLYGON(((…))) |
Theix |
56251 |
3 |
MULTIPOLYGON(((…))) |
Bréhan |
56024 |
4. Case with deleteTable
Import the city.shp layer into the CITY table
CALL SHPRead('/home/user/CITY.shp', 'CITY');
Now, import once again city.shp, using deleteTable=true
CALL SHPRead('/home/user/city.shp', 'CITY', true);
Returns : null (= no errors, the table CITY has been replaced).
Then, import once again city.shp, using deleteTable=false
CALL SHPRead('/home/user/city.shp', 'CITY', false);
Returns : The table "CITY" already exists