'ST_MakeEmptyRaster makes in geovalue worth 10000 and such when I want normal geodata
I have here made a demo of my attempt turn points on a map with values then use St_interpolateraster and than send it back as geojson. It returns values and they are interpolated well. My problem however is that I am trying to shape it geometricly and than add a little border the like it came in but it just wont work so I can use it on mapbox. My attempt on the shaping. My code is greatly inspired by this: https://www.crunchydata.com/blog/waiting-for-postgis-3.2-st_interpolateraster When using everything works fine
ST_AddBand(ST_MakeEmptyRaster(200, 400, 10, 10, 0.01, -0.005, 0, 0), '16BSI')
So the problem exists within this line or before
ST_AddBand(ST_MakeEmptyRaster(width, height, upperleftx, upperlefty, pixelsize), '16BSI')
DROP TABLE IF EXISTS PointTest;
DROP TABLE IF EXISTS ValueTest;
CREATE TABLE PointTest(geog geography(point), point_id INT);
INSERT INTO PointTest(geog,point_id)values ('POINT(10.5 9.5)',1),('POINT(10.5 9.5)',2),('POINT(10.5 8.5)',3),('POINT(11.5 9.5)',4);
CREATE TABLE ValueTest(temp INT, point_id INT);
INSERT INTO ValueTest(temp,point_id) values(1000,1),(1000,2),(500,3),(500,4);
WITH myvalues AS(
SELECT
ST_Collect(ST_PointZ(st_x(geog::geometry), st_y(geog::geometry), "zvalue"::float)) as geom
from (select geog,temp as zvalue from PointTest s inner join ValueTest p on s.point_id=p.point_id) as sources
),inputs AS (
SELECT
500::float8 AS pixelsize,
'invdist:power:3:smoothing:2.0' AS algorithm,
ST_Expand(geom, 10000) AS ext
FROM myvalues
),
sizes AS (
SELECT
ceil((ST_XMax(ext) - ST_XMin(ext))/pixelsize)::integer AS width,
ceil((ST_YMax(ext) - ST_YMin(ext))/pixelsize)::integer AS height,
ST_XMin(ext) AS upperleftx,
ST_YMax(ext) AS upperlefty
FROM inputs
),
interpolate AS(
SELECT ST_InterpolateRaster(
geom,
'invdist:smoothing:2.0',
ST_AddBand(ST_MakeEmptyRaster(width, height, upperleftx, upperlefty, pixelsize),'16BSI')
) as geom from myvalues,sizes,inputs)
SELECT row_to_json(fc)
FROM
(SELECT 'FeatureCollection' as type, array_to_json(array_agg(feats)) as features
FROM
(SELECT 'Feature' as type,
st_asgeojson((gv).geom)::json as geometry,
row_to_json((SELECT props FROM (SELECT (gv).val as value) as props )) as properties
FROM
(SELECT
ST_PixelAsPolygons(
ST_SetValue(
ST_SetValue(
ST_AddBand(
(select geom from interpolate),
'8BUI'::text, 1, 0),
2, 2, 10),
1, 1, NULL)
) gv
) json
) feats
) fc;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
