Loading shp file ( geodatic geography ) using COPY command Not Loading Data
Hi
I am trying to load shp file using copy command.
As per the instruction provided on vertica documentation page
https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/Place/STV_ShpSourceAndSTV_ShpParser.htm
I am able to create table
but on executing COPY command it is neither throwing any error nor loading the data.
It is showing Zero rows loaded.
COPY indna2_region(Name,Name_Lng,Off_Code,miCode,SmartLabel,FeatureID,geom ) WITH SOURCE STV_ShpSource(file='/tmp/GeoSpatial_File/indna2_region.shp', SRID=4326) PARSER STV_ShpParser();
Please suggest.
Thanks in advance.
With regards,
Vijay
0
Comments
Hi Vijay,
Let's start by checking the CopyErrorLogs in the catalog directory. Let me know what you find in the CopyErrorLogs. There might be an error message there that could help us diagonse this issue.
Also, could you please send me the shp file? Please do this by sending me a direct message with the shp file as an attachment. Please do not send any sensitive data.
-Casey
Thanks Casey for reply,
I digged down to CopyErrorLogs in the catalog directory.
Just for confirmation directory is /home/dbadmin/VMart/v_vmart_node0001_catalog/CopyErrorLogs
Please find below the snapshot of latest log:
COPY: Input record 641 has been rejected (Z and M values are not supported). Please see /home/dbadmin/VMart/v_vmart_node0001_catalog/CopyErrorLogs/indna2_region-STDIN-copy-from-rejected-data.f, record 641 for the rejected record.
COPY: Input record 642 has been rejected (Z and M values are not supported). Please see /home/dbadmin/VMart/v_vmart_node0001_catalog/CopyErrorLogs/indna2_region-STDIN-copy-from-rejected-data.f, record 642 for the rejected record.
COPY: Loaded 0 rows, rejected 642 rows.
-----------------------------------------------------------------------------------
This logs probably means that datatype mismatch occurs.
But the table create defination is defined by vertica itself by command
SELECT STV_ShpCreateTable
Then how come datatype mismatch occurs.
With regards,
Vijay
Hi Vijay,
Glad we were able to find the error in the CopyErrorsLog. That's a good first step.
What is the CREATE TABLE statement that STV_ShpCreateTable is returning?
Also, could you share your .shp file with me? It would help me reproduce this on my end. You can share the .shp file with me by using a private message. Please do not share sensitive information.
-Casey
Hi Casey,
Please find below create statement Vertica generated by command:
VMart=> SELECT STV_ShpCreateTable (USING PARAMETERS file='/home/dbadmin/TEST/indna2_region.shp') OVER() as create_table_states;
create_table_states
----------------------------------
CREATE TABLE indna2_region(
gid IDENTITY(64) PRIMARY KEY,
Name VARCHAR(100),
Name_Lng VARCHAR(3),
Off_Code VARCHAR(11),
miCode FLOAT8,
SmartLabel VARCHAR(100),
FeatureID VARCHAR(16),
geom GEOMETRY(158858)
);
Also I could not find any option/mail-id to send you private message.
Kindly help for the same.
----------------------------------------------------------------------------------------
After exporting the .shp file to .xls and further to .xml, glimse of type of data can be seen as below:
<table border='1' cellpadding='0' cellspacing='0' style='border-collapse: collapse' width='100%'>
<tr>
<td>Polygon Id</td>
<td>Part Id</td>
<td>Point Id</td>
<td>X</td>
<td>Y</td>
<td>Z</td>
<td>M</td>
</tr>
<tr>
<td>1</td>
<td>1</td>
<td>1</td>
<td>79.977305</td>
<td>19.39714</td>
<td>0</td>
<td>NaN</td>
----------------------------------------------------------------------------------------
I am not sure whether this .xml and glimpse of data will be helpful or not.
Also i could not corelate the generated create statement with this as data doesnot match for any of the column
With regards,
Vijay
Hi Vijay,
Here is short tutorial on how to send private messages:
1. Click the mail icon beneath the Search HP.com search bar.
2. Click Compose New Message.
3. Compose your Private Message.
Important: Do not include sensitive data.
4. Click Send Message.
I'm going to spend some more time today investigating the xml you have provided. In the meantime please send your shp file to me via Private Message.
Thanks for your patience.
-Casey
Hi Vijay,
Your shapefile data contains Z and M values. Unfortunately Place does not support 3D/4D data for now. A possible workaround is to re-create the shapefiles with 2D data only using a 3rd party tool. Then load the 2D shapefiles to Place as usual. An open source tool for that purpose is ogr2ogr.
Something like this should work:
$ ogr2ogr -f "ESRI Shapefile" data2d your_original_file.shp -lco GEOMETRY=AS_WKT -dim 2
Ariel