[postgis-users] Large WKT from file

[Nicolas Ribot]

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
>