[postgis-users] Importing data in Lambert72 (Belgium)

[Jo]

I will be needing the database to integrate data from the other tables. I
want to create a field containing all the bus lines that serve this stop,
but I was able to do that before performing joins. That's why I was trying
to do all of it in PostGIS. I have not been very succesful with GDAL in
Python, when I was trying to do this before.
I started with fields named x and y, but since the target is to have lon
and lat in there, I renamed them. COPY won't allow me to import a csv file
if the number of columns in the DB doesn't correspond to the number of
columns in the csv file.

Here is a sample line from the csv file:

5349;308999;Dorp;Dorp;Moerzeke;Hamme;134858;194785;false;true

134858;194785 is what I also find in my lon and lat columns in the DB after
COPY ran successfully. I know the trigger was invoked, as I had to debug it
before it would work...

I had expected something like 2.34 and 50.1 in those columns. (those are
not the real corresponding coordinates).

I found an example doing the reverse, but they had to use string
concatenation to  achieve what they wanted to do. Should I also let it
create a string and then parse that? That seems kind of backwards to me...

Jo




2012/11/14 Nicolas Ribot <nicolas.ribot at gmail.com>

> Hi,
>
> If the lat and lon fields of the stops table are already in WGS84, as
> their names suggest (latitude and longitude), then you don't have to
> transform them.
>
> Could you give us a sample of the table, to see the coordinates in lat and
> lon fields ?
> In a first step, I would add 2 new columns to the stops table storing
> transformed coordinates (WGS84) and keep columns storing Lambert72 values,
> to check.
>
> Otherwise, if the sole purpose is to produce an XML file, Paolo's approach
> would be the most straightforward: use ogr to both reproject the data and
> change their format.
>
> Nicolas
>
>
>
>
>
> On 14 November 2012 01:36, Jo <winfixit at gmail.com> wrote:
>
>> Hi Nicolas and Paolo,
>>
>> This is what I have been able to come up with:
>>
>> DROP EXTENSION postgis;
>> CREATE EXTENSION postgis;
>>
>> DROP TABLE IF EXISTS stops;
>>
>> CREATE TABLE stops
>> (
>>   stopid bigint NOT NULL,
>>   stopidentifier bigint,
>>   description text,
>>   street text,
>>   municipality text,
>>   parentmunicipality text,
>>   lat double precision,
>>   lon double precision,
>>   stopisaccessible boolean,
>>   stopispublic boolean,
>>   PRIMARY KEY (stopid)
>> )
>> WITH (
>>   OIDS=FALSE
>> );
>> ALTER TABLE stops
>>   OWNER TO postgres;
>>
>> CREATE OR REPLACE FUNCTION addwgs84() RETURNS trigger AS
>>   $body$
>>   DECLARE
>>     node geometry;
>>   BEGIN
>>     node := st_transform(st_setSRID(st_makePoint(NEW.lat, NEW.lon),
>> 31370),4326);
>>     NEW.lat := st_x(node);
>>     NEW.lon := st_y(node);
>>     RETURN NEW;
>>   END;
>>   $body$
>>   LANGUAGE 'plpgsql';
>>
>> CREATE TRIGGER wgs84
>>   AFTER INSERT OR UPDATE ON stops
>>   FOR EACH ROW EXECUTE PROCEDURE addwgs84();
>>
>> COPY stops FROM 'C:/stops.csv' DELIMITERS ';' CSV HEADER;
>>
>>
>> Unfortunately, this doesn't result in WGS84 lat and lon, but gives me
>> lambert72 once again. I must be missing something crucial somewhere. I
>> don't need the geometry in the table, so I took it out again. I'm not going
>> to use it to calculate distances. This is only an intermediate step in
>> converting all of it to an XML-format suitable for Openstreetmap.org.
>>
>> Thanks for your help. Hopefully I'm near to the goal I wanted to reach,
>> now and you can nudge me a bit further in the right direction.
>>
>> Jo
>>
>>
>> 2012/11/13 Nicolas Ribot <nicolas.ribot at gmail.com>
>>
>>> Hi,
>>>
>>> Use the  COPY command to load CSV to a table.
>>>
>>> The rest depends if you want to display/use the geometry, or you're only
>>> interested in the coordinates:
>>> • Add a geometry column
>>> • st_makePoint to build a point from coordinates
>>> • A trigger to create the WGS84 point from coordinates with
>>> st_transform: st_transform(st_setSRID(st_makePoint(coordinateX, coordinateY),
>>> 31300), 4326)
>>>
>>> Nicolas
>>>
>>>
>>>  On 13 November 2012 13:54, Jo <winfixit at gmail.com> wrote:
>>>
>>>>  Hi,
>>>>
>>>> I've been reading docs, but I'm afraid I can't figure it out on my own.
>>>>
>>>> I have a csv-file:
>>>> POI_ID;POI_name;coordinateX;coordinateY
>>>>
>>>>
>>>> Where X and Y are in Lambert72.
>>>>
>>>>
>>>> How can I get these in a table that includes 2 columns with those same
>>>> coordinates in WGS84? Should I have a column with a geometry type and
>>>> a point in it? Can I create a trigger function which updates the WGS84
>>>> as the Lambert72 are inserted?
>>>>
>>>> Jo
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>
>>>
>>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121114/6aaac2c7/attachment.html>