[postgis-users] Large WKT from file

[yvecai]

Thanks a lot Nicolas, I've also checked a few SQL tutorials and that 
helped a lot :)

I applied your suggestion with success, however I created a 
non-temporary table that I can re-use since I have a few .wkt to loop 
trough.
However, truncating or droping the tmpgeom is very long ??

I use a faster "DELETE FROM tmpgeom WHERE wkt is not null;"

Yves

Le 21/02/2012 13:16, Nicolas Ribot a écrit :
> Hi,
>
> You could convert the WKT Text to a postgis geometry before using it:
>
> -- tmp table with 2 columns: wkt text and geometry
> CREATE TEMP TABLE tmpgeom (wkt text, geom geometry);
>
> -- load WKT from file
> COPY tmpgeom (wkt) from '/home/website/SRTM/contours-extracts/france.wkt';
>
> -- convert wkt to geometry, set SRID accordingly
> update tmpgeom set geom = st_geomFromText(wkt, 4326);
>
> -- use this column from tmpgeom in your query:
> SELECT ST_simplify(intersection(way, geom),0.00005),
> height
>   from contours , tmpgeom
>   where ST_Intersects(way, tmpgeom.geom);
>
> (By the way, the given WKT is not a valid geometry:
> "Holes are nested[1.977228 42.494283]"
>
> The geometry can be cleanup with the st_buffer trick:
>
> select st_isvalid(st_buffer(geom, 0)) from tmpgeom;
> )
>
> Nicolas
>
> On 20 February 2012 22:59, yvecai<yvecai at gmail.com>  wrote:
>> Thanks Josh, but I'd like to avoid digging into the source, it must be
>> another way.
>> I come out to something like that, but I can't figure out how to use my
>> wkt() as a value for ST_geometryfromwkt() ??
>>
>> CREATE TEMP TABLE x (x text);
>> COPY x from '/home/website/SRTM/contours-extracts/france.wkt';
>> create function wkt() returns text as $$
>>      begin
>>          return (SELECT x from x);
>>      end;
>> $$ LANGUAGE plpgsql;
>>
>> SELECT ST_simplify(intersection(way,ST_GeomFromWKT((wkt(),-1)),0.00005),
>> height
>>   from contours where ST_Intersects(way, ST_GeomFromWKT(wkt(),-1));
>>
>> Yves
>>
>> Le 20/02/2012 21:24, yvecai a écrit :
>>
>> Hi all,
>> It's maybe more a bash question than a Postgis one, but ...
>>
>> I'm extracting shapefiles from a SRTM contour lines postgis base, using WKT
>> multipolygons to cut extract by country.
>> This gives something like:
>>
>> pgsql2shp -f shp/my_shapefile.shp -u mapnik contour \
>> "SELECT ST_simplify(intersection(way,GeomFromText('$poly',-1)),0.00005),
>> height \
>> from contours where \
>> ST_Intersects(way, GeomFromText('$poly',-1));"
>>
>> where $poly is a large WKT (like this one:
>> http://pistes-nordiques.org/download/france.wkt).
>>
>> I have the following error: /usr/bin/pgsql2shp: Argument list too long
>>
>> I played a little with xargs, but with no success. Is there a way to load
>> the wkt from a file with postgres, or something else?
>>
>> yves
>>
>>
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>