Le portail francophone de la géomatique

Dernière modification par Lucie D. (Tue 20 October 2015 15:15)

CREATE TABLE lucie.raster
AS
SELECT ST_AsRaster(comm.geom,import_raster6.rast,'8BUI',1,0,false)
FROM lucie.comm,lucie.import_raster6;
CREATE TABLE lucie.raster2
AS
SELECT ST_AsRaster(comm.geom,50,50,'8BUI',1,0,st_upperleftx(import_raster6.rast),st_upperlefty(import_raster6.rast),st_skewx(import_raster6.rast),st_skewy(import_raster6.rast),false) as rast2
FROM lucie.comm,lucie.import_raster6;
CREATE TABLE lucie.raster3
AS
SELECT ST_AsRaster(comm.geom,2.5,-2.5,'8BUI',1,0,null,null,0,0,false) as rast3
FROM lucie.comm,lucie.import_raster6;
CREATE TABLE essai_lucie.raster_bati
AS
SELECT ST_AsRaster(bati.geom,50,50,'8BUI',1,0,NULL,NULL,0,0,false) as rastbati
FROM essai_lucie.bati,essai_lucie.import_raster4;
CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));
CREATE TABLE essai_lucie.raster_bati
AS
SELECT ST_AsRaster(bati.geom,50,50,'8BUI',1,0,NULL,NULL,0,0,false) as rastbati
FROM essai_lucie.bati,essai_lucie.import_raster4;
raster ST_AsRaster(geometry geom, double precision scalex, double precision scaley, double precision gridx, double precision gridy, text pixeltype, double precision value=1, double precision nodataval=0, double precision skewx=0, double precision skewy=0, boolean touched=false);
SELECT ST_Tile(st_AsRaster(the_geom, 50, 50,'8BUI',1,0,NULL,NULL,0,0,false),1,250,250) FROM formation.parcelle;
CREATE TABLE lucie.raster
AS
SELECT ST_AsRaster(bati.geom,50,50,'8BUI',1,0,NULL,NULL,0,0,false) as rastbati
FROM lucie.bati;
SELECT ST_AsRaster(bati.geom,50,50,'8BUI',1,0,NULL,NULL,0,0,false) as rastbati
FROM lucie.bati b join lucie.import_raster4 r on st_intersects (b.geom, r.rast);
SELECT ST_AsRaster(bati.geom,50,50,'8BUI',b.code,0,NULL,NULL,0,0,false) as rastbati
FROM lucie.bati b join lucie.import_raster4 r on st_intersects (b.geom, r.rast);
-- ici, le raster servant au découpage est aussi utilisé pour spécifier les parametres des rasters produits.
-- si vous devez changer le type du raster produit, il faut utiliser une autre version de st_asRaster

drop table if exists lucie.raster;
create table lucie.raster as (
  SELECT st_tile(
                st_union(
                  st_asRaster(b.geom, r.rast, '8BUI', b.code, 0, FALSE)), 50, 50) as rast
  FROM lucie.bati b JOIN lucie.import_raster4 r ON st_intersects(b.geom, r.rast)
);
raster ST_AsRaster(geometry geom, integer width, integer height, text pixeltype, double precision value=1, double precision nodataval=0, double precision upperleftx=NULL, double precision upperlefty=NULL, double precision skewx=0, double precision skewy=0, boolean touched=false);
raster ST_AsRaster(geometry geom, raster ref, text pixeltype, double precision value=1, double precision nodataval=0, boolean touched=false);
CREATE TABLE lucie.raster4
AS
SELECT ST_MapAlgebra(raster3.st_tile,ST_AddBand(ST_MakeEmptyRaster(r.rast),'8BUI'::text,-9999,-9999),'[rast1]','8BUI','SECOND') rast
FROM lucie.raster3,lucie.import_raster6 as r;
CREATE TABLE lucie.raster
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(b.geom,r.rast,'8BUI',b.hauteur,NULL,false)),50,50) as rastbati
FROM lucie.bati as b join lucie.import_raster6 as r on st_intersects (b.geom, r.rast);
CREATE TABLE lucie.raster2
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(b.geom,rast,'8BUI',b.hauteur,NULL)),50,50) as rastbati
FROM lucie.bati as b , (SELECT rast FROM lucie.import_raster6 LIMIT 1) as rast;
SELECT rast FROM lucie.import_raster6 LIMIT 1
CREATE TABLE lucie.raster5
AS
SELECT ST_ExtractToRaster(ST_AddBand(ST_MakeEmptyRaster(rast),'8BUI'::text,-9999,-9999),
'lucie',
'bati',
'geom',
'hauteur',
'VALUE_OF_BIGGEST')rast
FROM lucie.import_raster6;
la fonction st_addband(raster, text, unknown, unknown) n'est pas unique
ST_AddBand(ST_MakeEmptyRaster(rast),'8BUI'::text,NULL:int8NULL::int8),
SELECT st_envelope(raster) FROM moraster;
(select rast from raster limit 1)
CREATE TABLE lucie.raster
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(b.geom,r.rast,'8BUI',b.hauteur,NULL,false)),50,50) as rastbati
FROM lucie.bati as b join lucie.import_raster6 as r on st_intersects (b.geom, r.rast);
DROP TABLE IF EXISTS lucie.raster3;
CREATE TABLE lucie.raster3
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(b.geom,2.5,-2.5,'8BUI',b.hauteur,0,st_upperleftx(r.rast),st_upperlefty(r.rast),st_skewx(r.rast),st_skewy(r.rast))),50,50)
FROM lucie.import_raster6 as r, lucie.bati as b
WHERE st_intersects(b.geom,r.rast);
CREATE TABLE lucie.raster7
AS
SELECT ST_MapAlgebra(raster3.rast,ST_AddBand(ST_MakeEmptyRaster(r.rast),'8BUI'::text,NULL::int8,NULL::int8),'[rast1]','8BUI','SECOND') rast
FROM lucie.raster3,lucie.import_raster6 as r
WHERE St_Intersects(raster3.rast,r.rast);
CREATE TABLE lucie.veget
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(v.geom,2.5,-2.5,'8BUI',v.prec_plani,NULL::int8,st_upperleftx(r.rast),st_upperlefty(r.rast),st_skewx(r.rast),st_skewy(r.rast))),50,50)
FROM lucie.import_raster6 as r, lucie.vegetation as v
WHERE st_intersects(v.geom,r.rast);
DROP TABLE IF EXISTS lucie.veget_intersection;
CREATE TABLE lucie.veget_intersection
AS
SELECT v.*, ST_intersection(v.geom,r.geom) as geometry
FROM lucie.vegetation as v,lucie.raster_emprise as r
WHERE st_intersects(v.geom,r.geom);
DROP TABLE IF EXISTS lucie.raster11;
CREATE TABLE lucie.raster11
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(v.geometry,2.5,-2.5,'8BUI',v.prec_plani,NULL::int8,st_upperleftx(r.rast),st_upperlefty(r.rast),st_skewx(r.rast),st_skewy(r.rast))),50,50)
FROM lucie.import_raster6 as r, lucie.veget_intersection as v
WHERE st_intersects(v.geometry,r.rast);
ERREUR:  Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
HINT:  Change argument 1: 'GEOMETRYCOLLECTION(POINT(475000 6739627.9),POLYGON((474940.3 6739683.7,474943...'
CONTEXT:  fonction PL/pgsql _st_intersects(geometry,raster,integer), ligne 39 à IF
********** Erreur **********

ERREUR: Relate Operation called with a LWGEOMCOLLECTION type.  This is unsupported.
État SQL :XX000
Astuce : Change argument 1: 'GEOMETRYCOLLECTION(POINT(475000 6739627.9),POLYGON((474940.3 6739683.7,474943...'
Contexte : fonction PL/pgsql _st_intersects(geometry,raster,integer), ligne 39 à IF
DROP TABLE IF EXISTS lucie.veget_intersection;
CREATE TABLE lucie.veget_intersection
AS
SELECT v.*, ST_Multi(ST_Buffer(ST_intersection(v.geom,r.geom),0.0)) as geometry
FROM lucie.raster_emprise as r
INNER JOIN lucie.vegetation as v
ON st_intersects(r.geom,v.geom)
WHERE Not ST_IsEmpty(ST_Buffer(ST_Intersection(r.geom,v.geom),0.0));

DROP TABLE IF EXISTS lucie.raster10;
CREATE TABLE lucie.raster10
AS
SELECT ST_Tile(ST_Union(ST_AsRaster(v.geometry,2.5,-2.5,'8BUI',v.prec_plani,NULL::int8,st_upperleftx(r.rast),st_upperlefty(r.rast),st_skewx(r.rast),st_skewy(r.rast))),50,50)
FROM lucie.import_raster6 as r, lucie.veget_intersection as v
WHERE st_intersects(v.geometry,r.rast);
CREATE TABLE resultat4 AS
WITH resultat AS (
  SELECT rid, ST_MapAlgebra(
                ST_Union(ST_AsRaster(geom, rast, '8BUI', code, NULL::int8)),
                ST_AddBand(ST_MakeEmptyRaster(rast), '8BUI'::text, NULL::int8, NULL::int8), 
                '[rast1]', '8BUI', 'SECOND') as rast
  FROM vecteur, raster
  WHERE ST_Intersects(geom, rast)
GROUP BY rid, rast
)
SELECT a.rid,
       CASE
         WHEN b.rid IS NULL THEN ST_AddBand (ST_MakeEmptyRaster(a.rast), '8BUI'::text, NULL::int8, NULL::int8)
         ELSE b.rast
       END as rast
FROM raster as a LEFT OUTER JOIN resultat as b 
ON a.rid = b.rid;
CREATE TABLE lucie.compil
AS
SELECT
CASE
WHEN a.rast>0 THEN a.rast
ELSE b.rast 
END as rast
FROM lucie.raster1 as a join lucie.raster2 as b on st_intersects(a.geom,b.geom);
CREATE TABLE lucie.compil
AS
SELECT
CASE
WHEN st_intersects(a.rast,b.rast) THEN a.rast
ELSE b.rast
END as rast
FROM lucie.raster1 as a join lucie.raster2 as b on st_intersects(a.rast,b.rast);
SELECT (rast).val FROM monraster;
CREATE TABLE lucie.raster2
AS
SELECT rid,rast,ST_DumpAsPolygons(rast,1) FROM lucie.raster;
CREATE TABLE lucie.raster_final
AS
SELECT rid,rast,st_dumpaspolygons,(st_dumpaspolygons).val as valeur FROM lucie.raster2;
CREATE TABLE lucie.compil
AS
SELECT a.rast as rast_a, b.rast as rast_b,
CASE
WHEN a.valeur>0 THEN a.valeur
ELSE b.valeur
END as valeur
FROM lucie.essai as a join lucie.raster_final as b on st_intersects(a.rast,b.rast);
DROP TABLE IF EXISTS lucie.compil;
CREATE TABLE lucie.compil
AS
SELECT
CASE
WHEN a.valeur>0 THEN a.rast
ELSE b.rast
END as rast
FROM lucie.essai as a join lucie.raster_final as b on st_intersects(a.rast,b.rast);
DROP TABLE IF EXISTS lucie.compil2;
CREATE TABLE lucie.compil2
AS
SELECT ST_Tile(rast,50,50) as rast
FROM lucie.compil;

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Thu 15 May 2014 10:55

PostGis 2 : convertir table geometry en table raster

#2 Mon 19 October 2015 15:51

Re: PostGis 2 : convertir table geometry en table raster

#3 Tue 20 October 2015 15:13

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

#4 Tue 20 October 2015 15:22

Re: PostGis 2 : convertir table geometry en table raster

Code:

#5 Wed 21 October 2015 13:46

Re: PostGis 2 : convertir table geometry en table raster

Code:

#6 Mon 02 November 2015 13:53

Re: PostGis 2 : convertir table geometry en table raster

#7 Mon 02 November 2015 14:17

Re: PostGis 2 : convertir table geometry en table raster

Code:

#8 Tue 03 November 2015 10:13

Re: PostGis 2 : convertir table geometry en table raster

Code:

#9 Tue 03 November 2015 10:47

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

#10 Tue 03 November 2015 10:54

Re: PostGis 2 : convertir table geometry en table raster

Code:

#11 Tue 03 November 2015 11:14

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

#12 Tue 03 November 2015 12:05

Re: PostGis 2 : convertir table geometry en table raster

Code:

#13 Tue 03 November 2015 12:22

Re: PostGis 2 : convertir table geometry en table raster

#14 Tue 03 November 2015 14:38

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

#15 Thu 05 November 2015 09:05

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

Code:

Code:

Code:

Code:

#16 Thu 05 November 2015 09:41

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

#17 Thu 05 November 2015 10:23

Re: PostGis 2 : convertir table geometry en table raster

Code:

#18 Thu 05 November 2015 13:32

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

Code:

#19 Fri 20 November 2015 11:52

Re: PostGis 2 : convertir table geometry en table raster

Code:

Code:

Code:

Code:

Code:

#20 Mon 23 November 2015 15:35

Re: PostGis 2 : convertir table geometry en table raster

#21 Fri 27 November 2015 10:49

Re: PostGis 2 : convertir table geometry en table raster

Code:

#22 Fri 27 November 2015 11:03

Re: PostGis 2 : convertir table geometry en table raster

#23 Fri 27 November 2015 11:31

Re: PostGis 2 : convertir table geometry en table raster