1package main 2 3import ( 4 "context" 5 "encoding/json" 6 "fmt" 7 "net/http" 8 "net/url" 9 "sort" 10 "strconv" 11 "strings" 12 13 // Database 14 "github.com/jackc/pgtype" 15 16 // Logging 17 log "github.com/sirupsen/logrus" 18 19 // Configuration 20 "github.com/spf13/viper" 21) 22 23type LayerTable struct { 24 ID string 25 Schema string 26 Table string 27 Description string 28 Properties map[string]TableProperty 29 GeometryType string 30 IDColumn string 31 GeometryColumn string 32 Srid int 33} 34 35type TableProperty struct { 36 Name string `json:"name"` 37 Type string `json:"type"` 38 Description string `json:"description"` 39 order int 40} 41 42type TableDetailJSON struct { 43 ID string `json:"id"` 44 Schema string `json:"schema"` 45 Name string `json:"name"` 46 Description string `json:"description,omitempty"` 47 Properties []TableProperty `json:"properties,omitempty"` 48 GeometryType string `json:"geometrytype,omitempty"` 49 Center [2]float64 `json:"center"` 50 Bounds [4]float64 `json:"bounds"` 51 MinZoom int `json:"minzoom"` 52 MaxZoom int `json:"maxzoom"` 53 TileURL string `json:"tileurl"` 54} 55 56/******************************************************************************** 57 * Layer Interface 58 */ 59 60func (lyr LayerTable) GetType() LayerType { 61 return LayerTypeTable 62} 63 64func (lyr LayerTable) GetID() string { 65 return lyr.ID 66} 67 68func (lyr LayerTable) GetDescription() string { 69 return lyr.Description 70} 71 72func (lyr LayerTable) GetName() string { 73 return lyr.Table 74} 75 76func (lyr LayerTable) GetSchema() string { 77 return lyr.Schema 78} 79 80func (lyr LayerTable) WriteLayerJSON(w http.ResponseWriter, req *http.Request) error { 81 jsonTableDetail, err := lyr.getTableDetailJSON(req) 82 if err != nil { 83 return err 84 } 85 w.Header().Add("Content-Type", "application/json") 86 json.NewEncoder(w).Encode(jsonTableDetail) 87 // all good, no error 88 return nil 89} 90 91func (lyr LayerTable) GetTileRequest(tile Tile, r *http.Request) TileRequest { 92 rp := lyr.getQueryParameters(r.URL.Query()) 93 sql, _ := lyr.requestSQL(&tile, &rp) 94 95 tr := TileRequest{ 96 LayerID: lyr.ID, 97 Tile: tile, 98 SQL: sql, 99 Args: nil, 100 } 101 return tr 102} 103 104/********************************************************************************/ 105 106type queryParameters struct { 107 Limit int 108 Properties []string 109 Resolution int 110 Buffer int 111} 112 113// getRequestIntParameter ignores missing parameters and non-integer parameters, 114// returning the "unknown integer" value for this case, which is -1 115func getQueryIntParameter(q url.Values, param string) int { 116 ok := false 117 sParam := make([]string, 0) 118 119 for k, v := range q { 120 if strings.EqualFold(k, param) { 121 sParam = v 122 ok = true 123 break 124 } 125 } 126 if ok { 127 iParam, err := strconv.Atoi(sParam[0]) 128 if err == nil { 129 return iParam 130 } 131 } 132 return -1 133} 134 135// getRequestPropertiesParameter compares the properties in the request 136// with the properties in the table layer, and returns a slice of 137// just those that occur in both, or a slice of all table properties 138// if there is not query parameter, or no matches 139func (lyr *LayerTable) getQueryPropertiesParameter(q url.Values) []string { 140 sAtts := make([]string, 0) 141 haveProperties := false 142 143 for k, v := range q { 144 if strings.EqualFold(k, "properties") { 145 sAtts = v 146 haveProperties = true 147 break 148 } 149 } 150 151 lyrAtts := (*lyr).Properties 152 queryAtts := make([]string, 0, len(lyrAtts)) 153 haveIDColumn := false 154 155 if haveProperties { 156 aAtts := strings.Split(sAtts[0], ",") 157 for _, att := range aAtts { 158 decAtt, err := url.QueryUnescape(att) 159 if err == nil { 160 decAtt = strings.Trim(decAtt, " ") 161 att, ok := lyrAtts[decAtt] 162 if ok { 163 if att.Name == lyr.IDColumn { 164 haveIDColumn = true 165 } 166 queryAtts = append(queryAtts, att.Name) 167 } 168 } 169 } 170 } 171 // No request parameter or no matches, so we want to 172 // return all the properties in the table layer 173 if len(queryAtts) == 0 { 174 for _, v := range lyrAtts { 175 queryAtts = append(queryAtts, v.Name) 176 } 177 } 178 if (!haveIDColumn) && lyr.IDColumn != "" { 179 queryAtts = append(queryAtts, lyr.IDColumn) 180 } 181 return queryAtts 182} 183 184// getRequestParameters reads user-settables parameters 185// from the request URL, or uses the system defaults 186// if the parameters are not set 187func (lyr *LayerTable) getQueryParameters(q url.Values) queryParameters { 188 rp := queryParameters{ 189 Limit: getQueryIntParameter(q, "limit"), 190 Resolution: getQueryIntParameter(q, "resolution"), 191 Buffer: getQueryIntParameter(q, "buffer"), 192 Properties: lyr.getQueryPropertiesParameter(q), 193 } 194 if rp.Limit < 0 { 195 rp.Limit = viper.GetInt("MaxFeaturesPerTile") 196 } 197 if rp.Resolution < 0 { 198 rp.Resolution = viper.GetInt("DefaultResolution") 199 } 200 if rp.Buffer < 0 { 201 rp.Buffer = viper.GetInt("DefaultBuffer") 202 } 203 return rp 204} 205 206/********************************************************************************/ 207 208func (lyr *LayerTable) getTableDetailJSON(req *http.Request) (TableDetailJSON, error) { 209 td := TableDetailJSON{ 210 ID: lyr.ID, 211 Schema: lyr.Schema, 212 Name: lyr.Table, 213 Description: lyr.Description, 214 GeometryType: lyr.GeometryType, 215 MinZoom: viper.GetInt("DefaultMinZoom"), 216 MaxZoom: viper.GetInt("DefaultMaxZoom"), 217 } 218 // TileURL is relative to server base 219 td.TileURL = fmt.Sprintf("%s/%s/{z}/{x}/{y}.pbf", serverURLBase(req), lyr.ID) 220 221 // Want to add the properties to the Json representation 222 // in table order, which is fiddly 223 tmpMap := make(map[int]TableProperty) 224 tmpKeys := make([]int, 0, len(lyr.Properties)) 225 for _, v := range lyr.Properties { 226 tmpMap[v.order] = v 227 tmpKeys = append(tmpKeys, v.order) 228 } 229 sort.Ints(tmpKeys) 230 for _, v := range tmpKeys { 231 td.Properties = append(td.Properties, tmpMap[v]) 232 } 233 234 // Read table bounds and convert to Json 235 // which prefers an array form 236 bnds, err := lyr.GetBounds() 237 if err != nil { 238 return td, err 239 } 240 td.Bounds[0] = bnds.Xmin 241 td.Bounds[1] = bnds.Ymin 242 td.Bounds[2] = bnds.Xmax 243 td.Bounds[3] = bnds.Ymax 244 td.Center[0] = (bnds.Xmin + bnds.Xmax) / 2.0 245 td.Center[1] = (bnds.Ymin + bnds.Ymax) / 2.0 246 return td, nil 247} 248 249func (lyr *LayerTable) GetBoundsExact() (Bounds, error) { 250 bounds := Bounds{} 251 extentSQL := fmt.Sprintf(` 252 WITH ext AS ( 253 SELECT 254 coalesce( 255 ST_Transform(ST_SetSRID(ST_Extent("%s"), %d), 4326), 256 ST_MakeEnvelope(-180, -90, 180, 90, 4326) 257 ) AS geom 258 FROM "%s"."%s" 259 ) 260 SELECT 261 ST_XMin(ext.geom) AS xmin, 262 ST_YMin(ext.geom) AS ymin, 263 ST_XMax(ext.geom) AS xmax, 264 ST_YMax(ext.geom) AS ymax 265 FROM ext 266 `, lyr.GeometryColumn, lyr.Srid, lyr.Schema, lyr.Table) 267 268 db, err := dbConnect() 269 if err != nil { 270 return bounds, err 271 } 272 var ( 273 xmin pgtype.Float8 274 xmax pgtype.Float8 275 ymin pgtype.Float8 276 ymax pgtype.Float8 277 ) 278 err = db.QueryRow(context.Background(), extentSQL).Scan(&xmin, &ymin, &xmax, &ymax) 279 if err != nil { 280 return bounds, tileAppError{ 281 SrcErr: err, 282 Message: "Unable to calculate table bounds", 283 } 284 } 285 286 bounds.SRID = 4326 287 bounds.Xmin = xmin.Float 288 bounds.Ymin = ymin.Float 289 bounds.Xmax = xmax.Float 290 bounds.Ymax = ymax.Float 291 bounds.sanitize() 292 return bounds, nil 293} 294 295func (lyr *LayerTable) GetBounds() (Bounds, error) { 296 bounds := Bounds{} 297 extentSQL := fmt.Sprintf(` 298 WITH ext AS ( 299 SELECT ST_Transform(ST_SetSRID(ST_EstimatedExtent('%s', '%s', '%s'), %d), 4326) AS geom 300 ) 301 SELECT 302 ST_XMin(ext.geom) AS xmin, 303 ST_YMin(ext.geom) AS ymin, 304 ST_XMax(ext.geom) AS xmax, 305 ST_YMax(ext.geom) AS ymax 306 FROM ext 307 `, lyr.Schema, lyr.Table, lyr.GeometryColumn, lyr.Srid) 308 309 db, err := dbConnect() 310 if err != nil { 311 return bounds, err 312 } 313 314 var ( 315 xmin pgtype.Float8 316 xmax pgtype.Float8 317 ymin pgtype.Float8 318 ymax pgtype.Float8 319 ) 320 err = db.QueryRow(context.Background(), extentSQL).Scan(&xmin, &ymin, &xmax, &ymax) 321 if err != nil { 322 return bounds, tileAppError{ 323 SrcErr: err, 324 Message: "Unable to calculate table bounds", 325 } 326 } 327 328 // Failed to get estimate? Get the exact bounds. 329 if xmin.Status == pgtype.Null { 330 warning := fmt.Sprintf("Estimated extent query failed, run 'ANALYZE %s.%s'", lyr.Schema, lyr.Table) 331 log.WithFields(log.Fields{ 332 "event": "request", 333 "topic": "detail", 334 "key": warning, 335 }).Warn(warning) 336 return lyr.GetBoundsExact() 337 } 338 339 bounds.SRID = 4326 340 bounds.Xmin = xmin.Float 341 bounds.Ymin = ymin.Float 342 bounds.Xmax = xmax.Float 343 bounds.Ymax = ymax.Float 344 bounds.sanitize() 345 return bounds, nil 346} 347 348func (lyr *LayerTable) requestSQL(tile *Tile, qp *queryParameters) (string, error) { 349 350 type sqlParameters struct { 351 TileSQL string 352 QuerySQL string 353 TileSrid int 354 Resolution int 355 Buffer int 356 Properties string 357 MvtParams string 358 Limit string 359 Schema string 360 Table string 361 GeometryColumn string 362 Srid int 363 } 364 365 // need both the exact tile boundary for clipping and an 366 // expanded version for querying 367 tileBounds := tile.Bounds 368 queryBounds := tile.Bounds 369 queryBounds.Expand(tile.width() * float64(qp.Buffer) / float64(qp.Resolution)) 370 tileSQL := tileBounds.SQL() 371 tileQuerySQL := queryBounds.SQL() 372 373 // SRID of the tile we are going to generate, which might be different 374 // from the layer SRID in the database 375 tileSrid := tile.Bounds.SRID 376 377 // preserve case and special characters in column names 378 // of SQL query by double quoting names 379 attrNames := make([]string, 0, len(qp.Properties)) 380 for _, a := range qp.Properties { 381 attrNames = append(attrNames, fmt.Sprintf("\"%s\"", a)) 382 } 383 384 // only specify MVT format parameters we have configured 385 mvtParams := make([]string, 0) 386 mvtParams = append(mvtParams, fmt.Sprintf("'%s', %d", lyr.ID, qp.Resolution)) 387 if lyr.GeometryColumn != "" { 388 mvtParams = append(mvtParams, fmt.Sprintf("'%s'", lyr.GeometryColumn)) 389 } 390 // The idColumn parameter is PostGIS3+ only 391 if globalPostGISVersion >= 3000000 && lyr.IDColumn != "" { 392 mvtParams = append(mvtParams, fmt.Sprintf("'%s'", lyr.IDColumn)) 393 } 394 395 sp := sqlParameters{ 396 TileSQL: tileSQL, 397 QuerySQL: tileQuerySQL, 398 TileSrid: tileSrid, 399 Resolution: qp.Resolution, 400 Buffer: qp.Buffer, 401 Properties: strings.Join(attrNames, ", "), 402 MvtParams: strings.Join(mvtParams, ", "), 403 Schema: lyr.Schema, 404 Table: lyr.Table, 405 GeometryColumn: lyr.GeometryColumn, 406 Srid: lyr.Srid, 407 } 408 409 if qp.Limit > 0 { 410 sp.Limit = fmt.Sprintf("LIMIT %d", qp.Limit) 411 } 412 413 // TODO: Remove ST_Force2D when fixes to line clipping are common 414 // in GEOS. See https://trac.osgeo.org/postgis/ticket/4690 415 tmplSQL := ` 416 SELECT ST_AsMVT(mvtgeom, {{ .MvtParams }}) FROM ( 417 SELECT ST_AsMVTGeom( 418 ST_Transform(ST_Force2D(t."{{ .GeometryColumn }}"), {{ .TileSrid }}), 419 bounds.geom_clip, 420 {{ .Resolution }}, 421 {{ .Buffer }} 422 ) AS "{{ .GeometryColumn }}" 423 {{ if .Properties }} 424 , {{ .Properties }} 425 {{ end }} 426 FROM "{{ .Schema }}"."{{ .Table }}" t, ( 427 SELECT {{ .TileSQL }} AS geom_clip, 428 {{ .QuerySQL }} AS geom_query 429 ) bounds 430 WHERE ST_Intersects(t."{{ .GeometryColumn }}", 431 ST_Transform(bounds.geom_query, {{ .Srid }})) 432 {{ .Limit }} 433 ) mvtgeom 434 ` 435 436 sql, err := renderSQLTemplate("tabletilesql", tmplSQL, sp) 437 if err != nil { 438 return "", err 439 } 440 return sql, err 441} 442 443func getTableLayers() ([]LayerTable, error) { 444 445 layerSQL := ` 446 SELECT 447 Format('%s.%s', n.nspname, c.relname) AS id, 448 n.nspname AS schema, 449 c.relname AS table, 450 coalesce(d.description, '') AS description, 451 a.attname AS geometry_column, 452 postgis_typmod_srid(a.atttypmod) AS srid, 453 trim(trailing 'ZM' from postgis_typmod_type(a.atttypmod)) AS geometry_type, 454 coalesce(case when it.typname is not null then ia.attname else null end, '') AS id_column, 455 ( 456 SELECT array_agg(ARRAY[sa.attname, st.typname, coalesce(da.description,''), sa.attnum::text]::text[] ORDER BY sa.attnum) 457 FROM pg_attribute sa 458 JOIN pg_type st ON sa.atttypid = st.oid 459 LEFT JOIN pg_description da ON (c.oid = da.objoid and sa.attnum = da.objsubid) 460 WHERE sa.attrelid = c.oid 461 AND sa.attnum > 0 462 AND NOT sa.attisdropped 463 AND st.typname NOT IN ('geometry', 'geography') 464 ) AS props 465 FROM pg_class c 466 JOIN pg_namespace n ON (c.relnamespace = n.oid) 467 JOIN pg_attribute a ON (a.attrelid = c.oid) 468 JOIN pg_type t ON (a.atttypid = t.oid) 469 LEFT JOIN pg_description d ON (c.oid = d.objoid and d.objsubid = 0) 470 LEFT JOIN pg_index i ON (c.oid = i.indrelid AND i.indisprimary AND i.indnatts = 1) 471 LEFT JOIN pg_attribute ia ON (ia.attrelid = i.indexrelid) 472 LEFT JOIN pg_type it ON (ia.atttypid = it.oid AND it.typname in ('int2', 'int4', 'int8')) 473 WHERE c.relkind IN ('r', 'v', 'm', 'p') 474 AND t.typname = 'geometry' 475 AND has_table_privilege(c.oid, 'select') 476 AND has_schema_privilege(n.oid, 'usage') 477 AND postgis_typmod_srid(a.atttypmod) > 0 478 ORDER BY 1 479 ` 480 481 db, connerr := dbConnect() 482 if connerr != nil { 483 return nil, connerr 484 } 485 486 rows, err := db.Query(context.Background(), layerSQL) 487 if err != nil { 488 return nil, connerr 489 } 490 491 // Reset array of layers 492 layerTables := make([]LayerTable, 0) 493 for rows.Next() { 494 495 var ( 496 id, schema, table, description, geometryColumn string 497 srid int 498 geometryType, idColumn string 499 atts pgtype.TextArray 500 ) 501 502 err := rows.Scan(&id, &schema, &table, &description, &geometryColumn, 503 &srid, &geometryType, &idColumn, &atts) 504 if err != nil { 505 return nil, err 506 } 507 508 // We use https://godoc.org/github.com/jackc/pgtype#TextArray 509 // here to scan the text[][] map of property name/type 510 // created in the query. It gets a little ugly demapping the 511 // pgx TextArray type, but it is at least native handling of 512 // the array. It's complex because of PgSQL ARRAY generality 513 // really, no fault of pgx 514 properties := make(map[string]TableProperty) 515 516 if atts.Status == pgtype.Present { 517 arrLen := atts.Dimensions[0].Length 518 arrStart := atts.Dimensions[0].LowerBound - 1 519 elmLen := atts.Dimensions[1].Length 520 for i := arrStart; i < arrLen; i++ { 521 pos := i * elmLen 522 elmID := atts.Elements[pos].String 523 elm := TableProperty{ 524 Name: elmID, 525 Type: atts.Elements[pos+1].String, 526 Description: atts.Elements[pos+2].String, 527 } 528 elm.order, _ = strconv.Atoi(atts.Elements[pos+3].String) 529 properties[elmID] = elm 530 } 531 } 532 533 // "schema.tablename" is our unique key for table layers 534 lyr := LayerTable{ 535 ID: id, 536 Schema: schema, 537 Table: table, 538 Description: description, 539 GeometryColumn: geometryColumn, 540 Srid: srid, 541 GeometryType: geometryType, 542 IDColumn: idColumn, 543 Properties: properties, 544 } 545 546 layerTables = append(layerTables, lyr) 547 } 548 // Check for errors from iterating over rows. 549 if err := rows.Err(); err != nil { 550 return nil, err 551 } 552 return layerTables, nil 553} 554