Loading shp file ( geodatic geography ) using COPY command Not Loading Data



I am trying to load shp file using copy command.


As per the instruction provided on vertica documentation page 


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,




  • Options

    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.







  • Options

    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,





  • Options

    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.






  • Options

    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 indna2_region(
    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%'>


    <td>Polygon Id</td>

    <td>Part Id</td>

    <td>Point Id</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,



  • Options

    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.







  • Options
    Ariel_CaryAriel_Cary Vertica Employee Employee

    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




Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file