Create a function that stores a X3D file with GeoReference
General
PostgreSQL
takes the unique id as text
takes the geometry
calculates the offset values for x-y-z axes
stores the X3D file with these offset values by defining them as "Origin"
Description :
to query the geometry use similar query:
WITH sel AS
(
SELECT ftr.objectid as oid, gd.geometry as geom
FROM geometry_data gd
LEFT JOIN feature ftr ON
ftr.id = gd.feature_id
WHERE gd.id = 1302
)
SELECT x3d.* FROM sel, __create_georeferenced_x3d_file(sel.oid,sel.geom,'UTM 33N') as x3d;
Simple Expression :
Example :
CREATE OR REPLACE FUNCTION citydb.__create_georeferenced_x3d_file(
objectid text,
geom geometry,
geosystem text,
file_name text DEFAULT 'georeferenced_x3d_file.x3d'::text,
description text DEFAULT 'no-description'::text,
modified_date date DEFAULT CURRENT_DATE)
RETURNS TABLE(object_id text, x3d_file text)
LANGUAGE 'plpgsql'
COST 100
STABLE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY
SELECT
oid2 as object_id,
CONCAT(part1, file_name1, part2, description1, part3, modification_date, part4, file_name2, part5, geosystem1, part6, origin, part7, origin2, part8, geosystem2, part9, origin3,part10, geosystem3,part11, x3d_kernel, part12) AS georeferenced_x3d_file
FROM
(
SELECT
$1::text as oid2,
'<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE X3D PUBLIC "ISO//Web3D//DTD X3D 3.3//EN" "https://www.web3d.org/specifications/x3d-3.3.dtd"><X3D profile="Interchange" version="3.3" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance" xsd:noNamespaceSchemaLocation =" https://www.web3d.org/specifications/x3d-3.3.xsd "><head><component level="1" name="Geospatial"/><meta name="title" content="' AS part1,
$4 as file_name1,
'"/><meta name="description" content="' AS part2,
$5 as description1,
'"/><meta name="modified" content="' as part3,
$6 as modification_date,
'"/></head><Scene><WorldInfo title="' as part4,
$4 as file_name2,
'"/><GeoViewpoint geoSystem="' as part5,
$3 as geosystem1,
'" orientation="1 0 0 -1.57" position="' as part6,
CONCAT(y_offset, ' ', x_offset, ' ', z_offset) as origin,
'" containerField="children"><!-- GeoOrigin ORIGIN is a DEF node that has 2 USE nodes: USE_1, USE_2 --><GeoOrigin DEF="ORIGIN" geoCoords="' as part7,
CONCAT(y_offset, ' ', x_offset, ' ', z_offset) as origin2,
'" geoSystem="' as part8,
'geosystem_here' as geosystem2,
'"/><!-- location (0,0,0) --></GeoViewpoint><GeoLocation geoCoords="' as part9,
CONCAT(y_offset, ' ', x_offset, ' ', z_offset) as origin3,
'" geoSystem="' as part10,
$3 as geosystem3,
'" containerField="children"><GeoOrigin USE="ORIGIN"/><!-- location (0,2,0) --><Shape>' as part11,
st_asx3d(st_translate($2, -x_offset,-y_offset,-z_offset)) as x3d_kernel,
'<!-- default polygons are single-sided, applies default Appearance and Material --></Shape></GeoLocation></Scene></X3D>' as part12
FROM
(
SELECT
round(st_xmin($2))::numeric AS x_offset,
round(st_ymin($2))::numeric AS y_offset,
round(st_zmin($2))::numeric AS z_offset
)
);
END
$BODY$;
2025 Apr