[postgis-users] Importing data in Lambert72 (Belgium)
winfixit at gmail.com
Wed Nov 14 03:23:08 PST 2012
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:
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...
2012/11/14 Nicolas Ribot <nicolas.ribot at gmail.com>
> 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.
> 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 (
>> ALTER TABLE stops
>> OWNER TO postgres;
>> CREATE OR REPLACE FUNCTION addwgs84() RETURNS trigger AS
>> node geometry;
>> node := st_transform(st_setSRID(st_makePoint(NEW.lat, NEW.lon),
>> NEW.lat := st_x(node);
>> NEW.lon := st_y(node);
>> RETURN NEW;
>> 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.
>> 2012/11/13 Nicolas Ribot <nicolas.ribot at gmail.com>
>>> 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)
>>> On 13 November 2012 13:54, Jo <winfixit at gmail.com> wrote:
>>>> I've been reading docs, but I'm afraid I can't figure it out on my own.
>>>> I have a csv-file:
>>>> 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?
>>>> postgis-users mailing list
>>>> postgis-users at lists.osgeo.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the postgis-users