Thread: Importing csv files containing multiple geometries in postgis

Importing csv files containing multiple geometries in postgis

From
Rose Beck
Date:
I have the following data in tab delimited csv file (containing
latitude and longitude of places):

    <place1> <geo> "POLYGON((-5.8335446 43.3655635,-5.8336337
43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
43.3657311,-5.8335446 43.3655635))" .
    <place2> <geo> "POINT((-5.8374796 43.3687922))" .
    <place3> <geo> "LINESTRING((-5.8427265 43.3678474,-5.8421236 43.3677908))" .
    <place4> <geo> "MULTIPOINT((-5.8445678 43.3589236,-5.8435222
43.358205,-5.8429834 43.3575551,-5.8429127 43.3572642))" .

I want to import this data into postgis. In order to do so, I need to
create a table in my database first. I know I can create a table for
one specific geometry(e.g. for POINT) using:

    create table myTable (firstColumn varchar(100));
    SELECT AddGeometryColumn( 'myTable', 'geom', 4269, 'POINT', 2 );

But I am not able to understand as to how can I import my csv file
containing varied geometries into postgres in a single table in a
single column myTable. I also want to construct a single "gist" index
on the top of myTable


--
With Warm Regards,
Rose


Re: Importing csv files containing multiple geometries in postgis

From
SELCUK AYDIN
Date:
Hello ,


This table keep all geometry type (point , polygon, linestring). Your data is including all of them I think. Could you
sendyour csv data . I will try  insert into  a spatial table maybe.
 


CREATE TABLE mygis_table
(
  objectid serial not null,
  poly geometry(Geometry,4326),
  CONSTRAINT mygis_table_pkey PRIMARY KEY (objectid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE mygis_table
  OWNER TO postgres;

-- Index: "gixB19E787C"

-- DROP INDEX "gixB19E787C";

CREATE INDEX "gixB19E787C"
  ON mygis_table
  USING gist
  (poly);


I hope I will help you.


Best wishes.



-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rose Beck
Sent: Thursday, March 10, 2016 8:39 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Importing csv files containing multiple geometries in postgis

I have the following data in tab delimited csv file (containing latitude and longitude of places):

    <place1> <geo> "POLYGON((-5.8335446 43.3655635,-5.8336337
43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
43.3657311,-5.8335446 43.3655635))" .
    <place2> <geo> "POINT((-5.8374796 43.3687922))" .
    <place3> <geo> "LINESTRING((-5.8427265 43.3678474,-5.8421236 43.3677908))" .
    <place4> <geo> "MULTIPOINT((-5.8445678 43.3589236,-5.8435222
43.358205,-5.8429834 43.3575551,-5.8429127 43.3572642))" .

I want to import this data into postgis. In order to do so, I need to create a table in my database first. I know I can
createa table for one specific geometry(e.g. for POINT) using:
 

    create table myTable (firstColumn varchar(100));
    SELECT AddGeometryColumn( 'myTable', 'geom', 4269, 'POINT', 2 );

But I am not able to understand as to how can I import my csv file containing varied geometries into postgres in a
singletable in a single column myTable. I also want to construct a single "gist" index on the top of myTable
 


--
With Warm Regards,
Rose


--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin









SELCUK AYDIN
PROJE UZMANI
selcuk.aydin@netcad.com.tr

Cyber Park B Blok No:409
06800, Bilkent / Ankara
Tel. :(312) 265 0510 - 1192
Faks: (312) 265 0520
[NETCAD] <http://www.netcad.com.tr/>


Re: Importing csv files containing multiple geometries in postgis

From
Rose Beck
Date:
Dear Selcuk,

Thanks a lot for your response. I have also attached my csv file :)

I tried to insert values into mygis_table, however, I am afraid I am
getting the following errors. Can you please help me identify as to
where am I going wrong:

testdbSpatial=# insert into mygis_table(objectid, poly)
values(1,GeomFromText('LINESTRING(198231 263418,198213 268322)',-1));
ERROR:  function geomfromtext(unknown, integer) does not exist
LINE 1: insert into mygis_table(objectid, poly) values(1,GeomFromTex...
                                                         ^
HINT:  No function matches the given name and argument types. You
might need to add explicit type casts.

On Thu, Mar 10, 2016 at 1:20 PM, SELCUK AYDIN
<selcuk.aydin@netcad.com.tr> wrote:
> Hello ,
>
>
> This table keep all geometry type (point , polygon, linestring). Your data is including all of them I think. Could
yousend your csv data . I will try  insert into  a spatial table maybe. 
>
>
> CREATE TABLE mygis_table
> (
>   objectid serial not null,
>   poly geometry(Geometry,4326),
>   CONSTRAINT mygis_table_pkey PRIMARY KEY (objectid)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE mygis_table
>   OWNER TO postgres;
>
> -- Index: "gixB19E787C"
>
> -- DROP INDEX "gixB19E787C";
>
> CREATE INDEX "gixB19E787C"
>   ON mygis_table
>   USING gist
>   (poly);
>
>
> I hope I will help you.
>
>
> Best wishes.
>
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rose Beck
> Sent: Thursday, March 10, 2016 8:39 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Importing csv files containing multiple geometries in postgis
>
> I have the following data in tab delimited csv file (containing latitude and longitude of places):
>
>     <place1> <geo> "POLYGON((-5.8335446 43.3655635,-5.8336337
> 43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
> 43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
> 43.3657311,-5.8335446 43.3655635))" .
>     <place2> <geo> "POINT((-5.8374796 43.3687922))" .
>     <place3> <geo> "LINESTRING((-5.8427265 43.3678474,-5.8421236 43.3677908))" .
>     <place4> <geo> "MULTIPOINT((-5.8445678 43.3589236,-5.8435222
> 43.358205,-5.8429834 43.3575551,-5.8429127 43.3572642))" .
>
> I want to import this data into postgis. In order to do so, I need to create a table in my database first. I know I
cancreate a table for one specific geometry(e.g. for POINT) using: 
>
>     create table myTable (firstColumn varchar(100));
>     SELECT AddGeometryColumn( 'myTable', 'geom', 4269, 'POINT', 2 );
>
> But I am not able to understand as to how can I import my csv file containing varied geometries into postgres in a
singletable in a single column myTable. I also want to construct a single "gist" index on the top of myTable 
>
>
> --
> With Warm Regards,
> Rose
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
>
>
>
>
>
> SELCUK AYDIN
> PROJE UZMANI
> selcuk.aydin@netcad.com.tr
>
> Cyber Park B Blok No:409
> 06800, Bilkent / Ankara
> Tel. :(312) 265 0510 - 1192
> Faks: (312) 265 0520
> [NETCAD] <http://www.netcad.com.tr/>
>



--
With Warm Regards,
Rose

Attachment

Re: Importing csv files containing multiple geometries in postgis

From
SELCUK AYDIN
Date:
Hello Rose,

I checked that queries it Works. This syntax is ok. You can try it

INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('POINT(-5.8374796 43.3687922)', 4326));
INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('LINESTRING(-5.8427265 43.3678474,-5.8421236 43.3677908)',
4326));
INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('MULTIPOINT(-5.8445678 43.3589236,-5.8435222
43.358205,-5.842983443.3575551,-5.8429127 43.3572642)', 4326));
 


But this guery geometry is have problem. Could you kontrol this geomerty. İs it ok.


INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('POLYGON(-5.8335446 43.3655635,-5.8336337 43.3655207,-5.8349218
43.3673341,-5.834519743.3674013,-5.8336182 43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
43.3657311,-5.833544643.3655635)', 4326));
 

in addition objectid is ıdid not use it . it is incrising autonumber.

Now ı am looking polygon geometry  is is valid.

Best wishes.

-----Original Message-----
From: Rose Beck [mailto:rosebeck.82@gmail.com]
Sent: Thursday, March 10, 2016 12:10 PM
To: SELCUK AYDIN <selcuk.aydin@netcad.com.tr>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Importing csv files containing multiple geometries in postgis

Dear Selcuk,

Thanks a lot for your response. I have also attached my csv file :)

I tried to insert values into mygis_table, however, I am afraid I am getting the following errors. Can you please help
meidentify as to where am I going wrong:
 

testdbSpatial=# insert into mygis_table(objectid, poly)
values(1,GeomFromText('LINESTRING(198231 263418,198213 268322)',-1));
ERROR:  function geomfromtext(unknown, integer) does not exist LINE 1: insert into mygis_table(objectid, poly)
values(1,GeomFromTex...
                                                         ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

On Thu, Mar 10, 2016 at 1:20 PM, SELCUK AYDIN <selcuk.aydin@netcad.com.tr> wrote:
> Hello ,
>
>
> This table keep all geometry type (point , polygon, linestring). Your data is including all of them I think. Could
yousend your csv data . I will try  insert into  a spatial table maybe.
 
>
>
> CREATE TABLE mygis_table
> (
>   objectid serial not null,
>   poly geometry(Geometry,4326),
>   CONSTRAINT mygis_table_pkey PRIMARY KEY (objectid)
> )
> WITH (
>   OIDS=FALSE
> );
> ALTER TABLE mygis_table
>   OWNER TO postgres;
>
> -- Index: "gixB19E787C"
>
> -- DROP INDEX "gixB19E787C";
>
> CREATE INDEX "gixB19E787C"
>   ON mygis_table
>   USING gist
>   (poly);
>
>
> I hope I will help you.
>
>
> Best wishes.
>
>
>
> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rose Beck
> Sent: Thursday, March 10, 2016 8:39 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] Importing csv files containing multiple geometries in
> postgis
>
> I have the following data in tab delimited csv file (containing latitude and longitude of places):
>
>     <place1> <geo> "POLYGON((-5.8335446 43.3655635,-5.8336337
> 43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
> 43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
> 43.3657311,-5.8335446 43.3655635))" .
>     <place2> <geo> "POINT((-5.8374796 43.3687922))" .
>     <place3> <geo> "LINESTRING((-5.8427265 43.3678474,-5.8421236 43.3677908))" .
>     <place4> <geo> "MULTIPOINT((-5.8445678 43.3589236,-5.8435222
> 43.358205,-5.8429834 43.3575551,-5.8429127 43.3572642))" .
>
> I want to import this data into postgis. In order to do so, I need to create a table in my database first. I know I
cancreate a table for one specific geometry(e.g. for POINT) using:
 
>
>     create table myTable (firstColumn varchar(100));
>     SELECT AddGeometryColumn( 'myTable', 'geom', 4269, 'POINT', 2 );
>
> But I am not able to understand as to how can I import my csv file
> containing varied geometries into postgres in a single table in a
> single column myTable. I also want to construct a single "gist" index
> on the top of myTable
>
>
> --
> With Warm Regards,
> Rose
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
>
>
>
>
>
>
> SELCUK AYDIN
> PROJE UZMANI
> selcuk.aydin@netcad.com.tr
>
> Cyber Park B Blok No:409
> 06800, Bilkent / Ankara
> Tel. :(312) 265 0510 - 1192
> Faks: (312) 265 0520
> [NETCAD] <http://www.netcad.com.tr/>
>



--
With Warm Regards,
Rose









SELCUK AYDIN
PROJE UZMANI
selcuk.aydin@netcad.com.tr

Cyber Park B Blok No:409
06800, Bilkent / Ankara
Tel. :(312) 265 0510 - 1192
Faks: (312) 265 0520
[NETCAD] <http://www.netcad.com.tr/>


Re: Importing csv files containing multiple geometries in postgis

From
Rose Beck
Date:
Dear Selcuk,

Thank you very much again for the reply.

Yes, I can control the geometry. Is there some way by which I may
convert the following "invalid" polygon geometry to "valid" in
postgis:

INSERT INTO mygis_table( poly)  VALUES(
ST_GeomFromText('POLYGON(-5.8335446 43.3655635,-5.8336337
43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
43.3657311,-5.8335446 43.3655635)', 4326));

On Thu, Mar 10, 2016 at 4:58 PM, SELCUK AYDIN
<selcuk.aydin@netcad.com.tr> wrote:
> Hello Rose,
>
> I checked that queries it Works. This syntax is ok. You can try it
>
> INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('POINT(-5.8374796 43.3687922)', 4326));
> INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('LINESTRING(-5.8427265 43.3678474,-5.8421236 43.3677908)',
4326));
> INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('MULTIPOINT(-5.8445678 43.3589236,-5.8435222
43.358205,-5.842983443.3575551,-5.8429127 43.3572642)', 4326)); 
>
>
> But this guery geometry is have problem. Could you kontrol this geomerty. İs it ok.
>
>
> INSERT INTO mygis_table( poly)  VALUES( ST_GeomFromText('POLYGON(-5.8335446 43.3655635,-5.8336337
43.3655207,-5.834921843.3673341,-5.8345197 43.3674013,-5.8336182 43.3660938,-5.8335037 43.365924,-5.8334505
43.3658461,-5.833470243.3657311,-5.8335446 43.3655635)', 4326)); 
>
> in addition objectid is ıdid not use it . it is incrising autonumber.
>
> Now ı am looking polygon geometry  is is valid.
>
> Best wishes.
>
> -----Original Message-----
> From: Rose Beck [mailto:rosebeck.82@gmail.com]
> Sent: Thursday, March 10, 2016 12:10 PM
> To: SELCUK AYDIN <selcuk.aydin@netcad.com.tr>
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Importing csv files containing multiple geometries in postgis
>
> Dear Selcuk,
>
> Thanks a lot for your response. I have also attached my csv file :)
>
> I tried to insert values into mygis_table, however, I am afraid I am getting the following errors. Can you please
helpme identify as to where am I going wrong: 
>
> testdbSpatial=# insert into mygis_table(objectid, poly)
> values(1,GeomFromText('LINESTRING(198231 263418,198213 268322)',-1));
> ERROR:  function geomfromtext(unknown, integer) does not exist LINE 1: insert into mygis_table(objectid, poly)
values(1,GeomFromTex...
>                                                          ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
>
> On Thu, Mar 10, 2016 at 1:20 PM, SELCUK AYDIN <selcuk.aydin@netcad.com.tr> wrote:
>> Hello ,
>>
>>
>> This table keep all geometry type (point , polygon, linestring). Your data is including all of them I think. Could
yousend your csv data . I will try  insert into  a spatial table maybe. 
>>
>>
>> CREATE TABLE mygis_table
>> (
>>   objectid serial not null,
>>   poly geometry(Geometry,4326),
>>   CONSTRAINT mygis_table_pkey PRIMARY KEY (objectid)
>> )
>> WITH (
>>   OIDS=FALSE
>> );
>> ALTER TABLE mygis_table
>>   OWNER TO postgres;
>>
>> -- Index: "gixB19E787C"
>>
>> -- DROP INDEX "gixB19E787C";
>>
>> CREATE INDEX "gixB19E787C"
>>   ON mygis_table
>>   USING gist
>>   (poly);
>>
>>
>> I hope I will help you.
>>
>>
>> Best wishes.
>>
>>
>>
>> -----Original Message-----
>> From: pgsql-admin-owner@postgresql.org
>> [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rose Beck
>> Sent: Thursday, March 10, 2016 8:39 AM
>> To: pgsql-admin@postgresql.org
>> Subject: [ADMIN] Importing csv files containing multiple geometries in
>> postgis
>>
>> I have the following data in tab delimited csv file (containing latitude and longitude of places):
>>
>>     <place1> <geo> "POLYGON((-5.8335446 43.3655635,-5.8336337
>> 43.3655207,-5.8349218 43.3673341,-5.8345197 43.3674013,-5.8336182
>> 43.3660938,-5.8335037 43.365924,-5.8334505 43.3658461,-5.8334702
>> 43.3657311,-5.8335446 43.3655635))" .
>>     <place2> <geo> "POINT((-5.8374796 43.3687922))" .
>>     <place3> <geo> "LINESTRING((-5.8427265 43.3678474,-5.8421236 43.3677908))" .
>>     <place4> <geo> "MULTIPOINT((-5.8445678 43.3589236,-5.8435222
>> 43.358205,-5.8429834 43.3575551,-5.8429127 43.3572642))" .
>>
>> I want to import this data into postgis. In order to do so, I need to create a table in my database first. I know I
cancreate a table for one specific geometry(e.g. for POINT) using: 
>>
>>     create table myTable (firstColumn varchar(100));
>>     SELECT AddGeometryColumn( 'myTable', 'geom', 4269, 'POINT', 2 );
>>
>> But I am not able to understand as to how can I import my csv file
>> containing varied geometries into postgres in a single table in a
>> single column myTable. I also want to construct a single "gist" index
>> on the top of myTable
>>
>>
>> --
>> With Warm Regards,
>> Rose
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> SELCUK AYDIN
>> PROJE UZMANI
>> selcuk.aydin@netcad.com.tr
>>
>> Cyber Park B Blok No:409
>> 06800, Bilkent / Ankara
>> Tel. :(312) 265 0510 - 1192
>> Faks: (312) 265 0520
>> [NETCAD] <http://www.netcad.com.tr/>
>>
>
>
>
> --
> With Warm Regards,
> Rose
>
>
>
>
>
>
>
>
>
> SELCUK AYDIN
> PROJE UZMANI
> selcuk.aydin@netcad.com.tr
>
> Cyber Park B Blok No:409
> 06800, Bilkent / Ankara
> Tel. :(312) 265 0510 - 1192
> Faks: (312) 265 0520
> [NETCAD] <http://www.netcad.com.tr/>
>



--
With Warm Regards,
Rose