Mercurial > gemma

comparison pkg/imports/wp.go @ 2093:a9d28c489761

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Waterway profile import: Do all the calculations in geography domain.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 01 Feb 2019 17:18:49 +0100
parents f70f67eaa7aa
children 7a3378c14397
comparison
equal deleted inserted replaced
2092:f70f67eaa7aa 2093:a9d28c489761
76 } 76 }
77 77
78 const ( 78 const (
79 createGeomTempTableSQL = ` 79 createGeomTempTableSQL = `
80 CREATE TEMP TABLE wp_geoms ( 80 CREATE TEMP TABLE wp_geoms (
81 geom geometry(linestring, 4326) 81 geom geography(linestring, 4326)
82 ) ON COMMIT DROP` 82 ) ON COMMIT DROP`
83 83
84 createTempIndexSQL = ` 84 createTempIndexSQL = `
85 CREATE INDEX ON wp_geoms USING GIST(geom)` 85 CREATE INDEX ON wp_geoms USING GIST(geom)`
86 86
92 )` 92 )`
93 93
94 insertWaterwayProfileSQL = ` 94 insertWaterwayProfileSQL = `
95 WITH point AS ( 95 WITH point AS (
96 SELECT 96 SELECT
97 ST_Buffer(geom::geometry, 0.0001) AS geom, 97 ST_Buffer(geom, 10) AS geom,
98 geom::geometry AS point 98 geom AS point
99 FROM waterway.distance_marks_virtual 99 FROM waterway.distance_marks_virtual
100 WHERE location_code = 100 WHERE location_code =
101 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) 101 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
102 ) 102 )
103 INSERT INTO waterway.waterway_profiles ( 103 INSERT INTO waterway.waterway_profiles (
111 fe100, 111 fe100,
112 date_info, 112 date_info,
113 source_organization 113 source_organization
114 ) VALUES ( 114 ) VALUES (
115 ($1, $2, $3, $4, $5), 115 ($1, $2, $3, $4, $5),
116 ( SELECT wp_geoms.geom::geography 116 ( SELECT wp_geoms.geom
117 FROM wp_geoms, point 117 FROM wp_geoms, point
118 WHERE wp_geoms.geom && point.geom 118 WHERE wp_geoms.geom && point.geom
119 ORDER BY ST_Distance(point.point, wp_geoms.geom) 119 ORDER BY ST_Distance(point.point, wp_geoms.geom, true)
120 LIMIT 1 120 LIMIT 1
121 ), 121 ),
122 $6, 122 $6,
123 $7, 123 $7,
124 $8, 124 $8,