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

[Jo]

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/7fec8672/attachment.html>