Suite

Requête PostGIS : toutes les lignes à moins d'une heure les unes des autres qui se croisent


J'utilise postgres (postgis) et je souhaite effectuer une requête qui renvoie toutes les lignes que les géométries se croisent et ont un horodatage à moins d'une heure les unes des autres.

À quoi ressemblerait l'instruction SQL ?

le tableau ressemble à ceci :

id { entier } nom { caractère variable } limites { géométrie } horodatage { horodatage avec fuseau horaire } // limites volontairement omises dans l'exemple de table ci-dessous (pas nécessaire) id name timestamp ---+-------- --+-------------------------------- 1 un "2010-09-24 21:10:39.515+00 " 2 deux "2010-09-16 09:21:09.362+00" 3 trois "2010-07-08 00:00:46.549+00"…

MODIFIER #1

N'incluant pas la correspondance d'intersection, voici un exemple : pour chaque ligne, donnez-moi toutes les autres lignes qui se trouvent dans l'heure :

select * from myTable order by t id | t ----+-------------------------------- 9 | 2011-07-15 18:20:20.05+02 10 | 2011-07-15 19:05:00.05+02 11 | 2011-07-15 19:40:20.05+02 13 | 2011-07-15 20:31:01.05+02 14 | 2011-07-15 20:35:11.05+02 (5 lignes) résultat de la requête nécessaire : id | matchid | heure d'origine | matchTime ----+----------+----------------------------+---- -------------------------- 9 | 10 | 2011-07-15 18:20:20.05+02 | 2011-07-15 19:05:00.05+02 10 | 9 | 2011-07-15 19:05:00.05+02 | 2011-07-15 18:20:20.05+02 10 | 11 | 2011-07-15 19:05:00.05+02 | 2011-07-15 19:40:20.05+02 11 | 10 | 2011-07-15 19:40:20.05+02 | 2011-07-15 19:05:00.05+02 11 | 13 | 2011-07-15 19:40:20.05+02 | 2011-07-15 20:31:01.05+02 11 | 14 | 2011-07-15 19:40:20.05+02 | 2011-07-15 20:35:11.05+02 13 | 11 | 2011-07-15 20:31:01.05+02 | 2011-07-15 19:40:20.05+02 13 | 14 | 2011-07-15 20:31:01.05+02 | 2011-07-15 20:35:11.05+02 14 | 11 | 2011-07-15 20:35:11.05+02 | 2011-07-15 19:40:20.05+02 14 | 13 | 2011-07-15 20:35:11.05+02 | 2011-07-15 20:31:01.05+02 (10 lignes)

Qu'en est-il de:

sélectionnez m1.id comme id, m2.id comme match_id, m1.startDate comme origTime, m2.startDate comme matchTime de myTable m1 jointure interne myTable m2 sur st_intersects(m1.geom, m2.geom) et (m1.startDate - m2. startDate <= intervalle '1 heure' ou -(m1.startDate - m2.startDate) <= intervalle '1 heure') et m1.id != m2.id

La requête Postgis ST_Intersects n'utilise pas l'index spatial existant

J'ai un tableau des banlieues et chaque banlieue a une valeur geom, représentant son multipolygone sur la carte. Il existe une autre table de maisons où chaque maison a une valeur geom de son point sur la carte.

Les deux colonnes geom sont indexées à l'aide de gist, et la table suburbs a également le nom de colonne indexé. La table des banlieues a plus de 8 000 enregistrements tandis que la table des maisons a plus de 300 000 enregistrements.

Maintenant, ma tâche est de trouver toutes les maisons dans une banlieue nommée « FOO ».

3.5s, retournant 486 enregistrements.

REQUÊTE #2 : (préfixe la fonction ST_INTERSECTS avec _ pour lui demander explicitement de ne pas utiliser l'index)

Résultat du plan de requête : (exactement le même que la requête 1)

1.7s, renvoyant 486 enregistrements.

REQUÊTE #3 : (Utilisation de l'opérateur && pour ajouter une vérification de chevauchement de la zone de limite avant la fonction ST_Intersects)

l'exécution de la requête a pris 0,15 s, renvoyant 486 enregistrements.

Apparemment, seule la requête n°3 bénéficie de l'index spatial, ce qui améliore considérablement les performances. Cependant, la syntaxe est laide et se répète dans une certaine mesure. Ma question est:


Vous voulez "toutes les lignes dans un rayon de 5 miles d'une coordonnée", Alors ceci est ne pas exactement un problème K-plus proche voisin (KNN). Connexe, mais votre cas est plus simple. "Trouvez les 10 lignes les plus proches de mes coordonnées" serait un problème KNN.

Convertissez vos coordonnées en valeurs géographiques :

Vous pouvez également utiliser le type de géométrie plus simple. Considérer:
4.2.2. Quand utiliser le type de données Geography sur le type de données Geometry

Ensuite, nous avons un tableau comme :

Tout ce dont tu as besoin c'est ST_DDans() - et un index spatial pour faire vite :

Ou alors vous pouvez utiliser vos colonnes d'origine et créer un index fonctionnel. Ceci et d'autres détails dans cette réponse étroitement liée sur dba.SE :

Vous devez d'abord créer une table à partir de votre fichier au format CSV, en utilisant la commande COPY (si le fichier est accessible à PostgreSQL serveur) ou la commande copy dans psql si le fichier n'est pas local sur le serveur. Voir les autres Q+A sur SO pour des exemples si vous avez des problèmes.

Une fois que vous avez vos données dans une table, vous devez convertir vos colonnes de longitude et de latitude au type de géographie PostGIS en ajoutant une colonne à votre table de type géographie (POINT, 4326), puis en renseignant cette colonne (appelée ici gps ) avec le valeurs:

Ajoutez un index sur cette colonne pour permettre des recherches efficaces :

Vous pouvez maintenant trouver les rangées à moins de 5 miles d'un emplacement donné, par ex. (-72.657, 42.0657) , comme suit :

Notez que ST_DWithin() sur une colonne géographique fonctionnera en mètres, vous devez donc multiplier votre rayon en miles avec les 1 609 mètres dans un mile.


Optimiser une requête Intersect entre deux énormes tables spatiales

J'ai du mal à essayer d'améliorer une intersection entre deux tables spatiales et j'aimerais recevoir des conseils sur la conception des tables, les requêtes ou les configurations dba.

Les tables:

La table teste.recorte_grade a actuellement 1 655 569 lignes, mais il s'agit d'un sous-échantillon réalisé pour ce test d'une table de 9 millions de lignes.

La table teste2.uso_2012 a 177 888 lignes et ce sont toutes les données qu'elle aura jamais.

Problème:

Tout ce que je veux, c'est la zone et le code de grille de chaque intersection entre les deux tables, en gros, le résultat de cette requête :

Cependant, cette requête a duré environ 16 heures sans aucun résultat lorsque j'ai décidé d'annuler son exécution. Si cela prenait autant de temps avec le sous-échantillon, imaginez avec l'ensemble de données complet.

Les deux tables ont été analysées sous vide auparavant.

J'ai pensé que ce pourrait être une bonne idée de séparer cela en plusieurs requêtes pour un code de grille à chaque fois. C'est pourquoi j'ai créé l'index de hachage.

Voici la distribution des données dans la table teste2.uso_2012 :

Voici quelques résultats de requêtes pour des codes de grille individuels :

teste.recorte_2012 et teste2.uso_2012 sont à peu près la même table où uso_2012 a 1 colonne de moins.

Comme vous pouvez le voir, cela ne semble pas très prometteur. Existe-t-il des recommandations pour accélérer ce processus ?

Je pense créer une procédure stockée pour boucler les 177 888 lignes et obtenir directement les intersections et l'aire de chacune d'entre elles. est-ce une bonne idée?

Configurations :

Informations sur le serveur :

  • PostgreSQL 9.2.14
  • CENTOS VERSION 6.4
  • 8 Go de mémoire SRAM
  • STOCKAGE V7000
  • Processeur INTEL(R) XEON(R) E5-2620 2 GHz
  • POSTGIS="2.0.2 r10789" GEOS="3.3.6-CAPI-1.7.6" PROJ="Rel. 4.8.0, 6 mars 2012" GDAL="GDAL 1.9.2, publié 2012/10/08" LIBXML ="2.7.6" RASTER

Le serveur est partagé entre d'autres bases de données, mais aucun processus lourd ne s'exécutait en parallèle en même temps que j'exécutais les requêtes.

J'ai quelques particularités très complexes avec près de 100k sommets. Concernant la version Postgres, seuls les DBA peuvent mettre à jour les infrastructures, et je n'en fais pas partie.


4.4. Récupération des données SIG

Les données peuvent être extraites de la base de données à l'aide de SQL ou du chargeur/dumper de fichiers Shape. Dans la section sur SQL, nous discuterons de certains des opérateurs disponibles pour effectuer des comparaisons et des requêtes sur des tables spatiales.

4.4.1. Utilisation de SQL

Le moyen le plus simple d'extraire des données de la base de données consiste à utiliser une requête de sélection SQL et à vider les colonnes résultantes dans un fichier texte analysable :

Cependant, il y aura des moments où une sorte de restriction sera nécessaire pour réduire le nombre de champs renvoyés. Dans le cas de restrictions basées sur les attributs, utilisez simplement la même syntaxe SQL que d'habitude avec une table non spatiale. En cas de restrictions spatiales, les opérateurs suivants sont disponibles/utiles :

Cet opérateur indique si le cadre englobant d'une géométrie coupe le cadre englobant d'une autre.

Cet opérateur teste si deux géométries sont géométriquement identiques. Par exemple, si 'POLYGON((0 0,1 1,1 0,0 0))' est le même que 'POLYGON((0 0,1 1,1 0,0 0))' (c'est le cas).

Cet opérateur est un peu plus naïf, il teste seulement si les boîtes englobantes des géométries sont les mêmes.

Ensuite, vous pouvez utiliser ces opérateurs dans les requêtes. Notez que lorsque vous spécifiez des géométries et des boîtes sur la ligne de commande SQL, vous devez explicitement transformer les représentations sous forme de chaîne en géométries à l'aide de la fonction "GeomFromText()". Ainsi, par exemple :

La requête ci-dessus renverrait l'enregistrement unique de la table "ROADS_GEOM" dans lequel la géométrie était égale à cette valeur.

Lorsque vous utilisez l'opérateur "&&", vous pouvez spécifier soit une BOX3D comme fonction de comparaison, soit une GEOMETRIE. Cependant, lorsque vous spécifiez une GEOMETRIE, sa boîte englobante sera utilisée pour la comparaison.

La requête ci-dessus utilisera la boîte englobante du polygone à des fins de comparaison.

La requête spatiale la plus courante sera probablement une requête "basée sur un cadre", utilisée par les logiciels clients, tels que les navigateurs de données et les mappeurs Web, pour saisir une valeur de "cadre cartographique" de données à afficher. En utilisant un objet "BOX3D" pour le cadre, une telle requête ressemble à ceci :

A noter l'utilisation du SRID, pour préciser la projection de la BOX3D. La valeur -1 est utilisée pour indiquer aucun SRID spécifié.

4.4.2. Utilisation du dumper

Le dumper de table pgsql2shp se connecte directement à la base de données et convertit une table (éventuellement définie par une requête) en un fichier de forme. La syntaxe de base est :

Les options de la ligne de commande sont :

Écrivez la sortie dans un nom de fichier particulier.

L'hôte de base de données auquel se connecter.

Le port auquel se connecter sur l'hôte de la base de données.

Le mot de passe à utiliser lors de la connexion à la base de données.

Le nom d'utilisateur à utiliser lors de la connexion à la base de données.

Dans le cas de tables avec plusieurs colonnes de géométrie, la colonne de géométrie à utiliser lors de l'écriture du fichier de forme.

Utilisez un curseur binaire. Cela rendra l'opération plus rapide, mais ne fonctionnera pas si un attribut NON géométrique dans le tableau n'a pas de conversion en texte.

Mode brut. Ne supprimez pas le champ gid ou n'échappez pas aux noms de colonnes.

Pour une compatibilité descendante : écrivez un fichier de forme en 3 dimensions lors du vidage d'anciennes bases de données postgis (pré-1.0.0) (la valeur par défaut est d'écrire un fichier de forme en 2 dimensions dans ce cas). À partir de postgis-1.0.0+, les dimensions sont entièrement encodées.


Pivot

Vous voulez que le résultat pivote avec une colonne par group_id sélectionné. Utiliser tableau croisé() du module supplémentaire tablefunc pour des résultats plus rapides. Si vous ne le connaissez pas, lisez d'abord les instructions de base ici :

Les éléments manquants sont NULL. Vous ne pouvez pas l'attraper avec COALESCE autour de count() dans la requête interne. Utilisez plutôt COALESCE dans la requête externe. Comme:

Notez que les heures sans aucune ligne sont toujours manquantes dans le résultat. Utilisez un OUTER JOIN à une grille de temps complète si vous voulez des lignes vides dans le résultat. Comme:


Ordre des colonnes dans un index composé dans PostgreSQL (et ordre des requêtes)

J'ai une table avec 50K lignes. Il s'agit en fait d'une table PostGIS.

La requête comporte 4 parties (1 obligatoire) (3 facultatives)

  1. boîte d'intersection (un rectangle géographique) avec 4 lat, long (j'utilise st_intersects) [Obligatoire]
  2. Plage de dates (min, max) sur un champ de date
  3. Type de fichier (un ensemble de jusqu'à 8 valeurs de texte) utilisant actuellement IN( . ) mais je peux en faire une table temporaire si nécessaire. Je vois que beaucoup de gens n'aiment pas IN.
  4. Pays (une valeur de texte).

Je m'attends à environ 100 à 4 000 lignes renvoyées

Si je crée un index composé sur la table, quelle colonne dois-je utiliser en premier. Le grain fin est probablement l'emplacement (les données sont réparties dans le monde entier). Je l'ai actuellement comme index GIST.

Les autres indices seraient BTREE.

Mon intuition dit d'utiliser un grain fin, et bien sûr en dernier. Par exemple. Il n'y a qu'environ 12 types de fichiers, ce serait donc de très gros compartiments pour l'index.

Que disent les gourous de PostgreSQL et PostGIS (qui connaissent les rouages ​​du système) ?

Permettez-moi d'affiner cette question.

  1. Je ne veux pas que quiconque ait à faire le travail que je devrais faire. Je respecte trop votre temps. Je reviendrai donc à l'analyse d'explication plus tard.
  2. Tout ce que je cherchais, c'était des pointeurs, des conseils et des lignes directrices.
  3. J'ai lu cet excellent petit article : https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes à propos des index
  4. Ce que je fais normalement, c'est créer 4 index distincts (geo-box, nom de pays, file_type et date) mais que voulez-vous voir ce que ferait une requête composite.

Dites-moi si l'une de ces hypothèses est fausse. (Je suis assez nouveau dans l'idée des indices composés)

  1. L'ordre est important. Choisissez comme premier index celui qui réduira le plus les lignes (dans mon cas, l'emplacement (géographie) qui est un simple polygone ou multi-polygone ferait le mieux).
  2. Parfois, les requêtes ignorent les index. Mais si je crée une requête composée avec la clé (#1, #2, #3, #4), même si l'utilisateur crée quelque chose qui demande #1, #3, le planificateur utilisera toujours la requête composite unique, car ils commandent est maintenu.
  3. Normalement, je créerais trois requêtes BTREE et une GIST (pour le type géographie). PostGIS ne prend pas en charge la création d'un composé à partir de plusieurs types d'index. Je vais donc devoir utiliser GIST l'indice composé. Mais cela ne devrait pas nuire aux choses.
  4. Si je crée des index supplémentaires composés ou à valeur unique, le planificateur est suffisamment intelligent pour choisir le plus intelligent.
  5. Le nom du pays peut avoir environ 250 valeurs différentes et est évidemment fortement lié à l'emplacement (geobox), mais si le meilleur index suivant pour réduire la taille de la ligne est file_type, je devrais l'utiliser ensuite. Je ne m'attends pas à ce que les utilisateurs utilisent souvent le pays ou la date dans leurs ensembles de requêtes.
  6. Je n'ai PAS à m'inquiéter de la création d'un index composé de 4 clés qui augmentera considérablement la taille des données d'index. C'est à dire. si un index à une clé représente 90% de l'amélioration des performances, cela ne fait pas de mal d'ajouter 3 éléments supplémentaires pour le rendre composé. Inversement, je devrais vraiment créer les deux index. Un index géographique unique, ainsi qu'un index composé, et laissez le planificateur déterminer lequel est le meilleur, et il prendra en compte la taille de la table d'index.

Encore une fois, je ne demande à personne de concevoir ma solution, je ne me moque pas du travail des autres. Mais j'ai besoin de choses que la documentation de PostGreSQL ne me dit pas sur la mise en œuvre

[La raison pour laquelle je n'ai pas encore de résultat EXPLAIN à afficher, c'est que je dois créer cette table de 25K lignes à partir d'une table de 24M. Cela prend plus de temps que je ne le pensais. Je regroupe les éléments en 1 000 groupes d'articles et laisse l'utilisateur interroger la table de 25 000 lignes. Mais ma prochaine question impliquera d'utiliser les résultats de cette requête pour accéder à la table de lignes MASTER 25M et extraire des éléments, et c'est là que les performances de l'index composé seront vraiment HIT].

EXPLIQUER les résultats de l'ANALYSE (je n'ai mis aucun index composé, et à la vitesse que je vois, je ne sais pas si j'en ai besoin).


4.7. Utiliser Mapserver

Révision SVN ( 8935 )

Le Minnesota Mapserver est un serveur de cartographie Web Internet conforme à la spécification OpenGIS Web Mapping Server.

4.7.1. Utilisation de base

Révision SVN ( 8935 )

Pour utiliser PostGIS avec Mapserver, vous devez savoir comment configurer Mapserver, ce qui dépasse le cadre de cette documentation. Cette section couvrira les problèmes spécifiques de PostGIS et les détails de configuration.

Pour utiliser PostGIS avec Mapserver, vous aurez besoin de :

Version 0.6 ou plus récente de PostGIS.

Version 3.5 ou plus récente de Mapserver.

Mapserver accède aux données PostGIS/PostgreSQL comme n'importe quel autre client PostgreSQL -- en utilisant libpq . Cela signifie que Mapserver peut être installé sur n'importe quelle machine avec un accès réseau au serveur PostGIS, tant que le système dispose des bibliothèques clientes libpq PostgreSQL.

Compilez et installez Mapserver, avec les options que vous désirez, y compris l'option de configuration "--with-postgis".

Dans votre fichier de carte Mapserver, ajoutez une couche PostGIS. Par example:

Dans l'exemple ci-dessus, les directives spécifiques à PostGIS sont les suivantes :

Pour les couches PostGIS, il s'agit toujours de "postgis".

La connexion à la base de données est régie par une 'chaîne de connexion' qui est un ensemble standard de clés et de valeurs comme celle-ci (avec les valeurs par défaut dans <>) :

user=<username> mot de passe=<password> dbname=<username> hostname=<server> port=<5432>

Une chaîne de connexion vide est toujours valide et n'importe quelle paire clé/valeur peut être omise. Au minimum, vous fournirez généralement le nom de la base de données et le nom d'utilisateur avec lesquels vous connecter.

La forme de ce paramètre est "<column> de <tablename>" où la colonne est la colonne spatiale à rendre sur la carte.

Le filtre doit être une chaîne SQL valide correspondant à la logique qui suit normalement le mot-clé "WHERE" dans une requête SQL. Ainsi, par exemple, pour afficher uniquement les routes à 6 voies ou plus, utilisez un filtre de "num_lanes >= 6".

Dans votre base de données spatiale, assurez-vous d'avoir des index spatiaux (GiST) créés pour toutes les couches que vous dessinerez.

Si vous interrogez vos couches à l'aide de Mapserver, vous aurez également besoin d'un "index oid".

Mapserver requiert des identifiants uniques pour chaque enregistrement spatial lors des requêtes, et le module PostGIS de Mapserver utilise la valeur oid de PostgreSQL pour fournir ces identifiants uniques. Un effet secondaire de ceci est que pour effectuer un accès aléatoire rapide aux enregistrements pendant les requêtes, un index sur l'oid est nécessaire.

Pour construire un "index oid", utilisez le SQL suivant :

4.7.2. Questions fréquemment posées

Révision SVN ( 8935 )

Lorsque j'utilise une EXPRESSION dans mon fichier de carte, la condition ne revient jamais comme vraie, même si je sais que les valeurs existent dans ma table.

Contrairement aux fichiers de forme, les noms de champs PostGIS doivent être référencés dans EXPRESSIONS en utilisant minuscule .

Le FILTRE que j'utilise pour mes fichiers Shape ne fonctionne pas pour ma table PostGIS des mêmes données.

Contrairement aux fichiers de formes, les filtres pour les couches PostGIS utilisent la syntaxe SQL (ils sont ajoutés à l'instruction SQL que le connecteur PostGIS génère pour les couches de dessin dans Mapserver).

Ma couche PostGIS s'affiche beaucoup plus lentement que ma couche de fichier Shape, est-ce normal ?

En général, attendez-vous à ce que les couches PostGIS soient 10 % plus lentes que les couches de fichiers Shape équivalentes, en raison de la surcharge supplémentaire impliquée dans les connexions à la base de données, les transformations de données et le transit des données entre la base de données et Mapserver.

Si vous rencontrez des problèmes de performances d'affichage importants, il est probable que vous n'ayez pas créé d'index spatial sur votre table.

Ma couche PostGIS s'affiche bien, mais les requêtes sont vraiment lentes. Ce qui est faux?

Pour que les requêtes soient rapides, vous devez avoir une clé unique pour votre table spatiale et vous devez avoir un index sur cette clé unique.

Vous pouvez spécifier quelle clé unique pour mapserver à utiliser avec la clause USING UNIQUE dans votre ligne DATA :

Si votre table n'a pas de colonne unique explicite, vous pouvez "truquer" une colonne unique en utilisant la ligne PostgreSQL "oid" pour votre colonne unique. "oid" est la colonne unique par défaut si vous n'en déclarez pas, donc l'amélioration de la vitesse de votre requête consiste à créer un index sur la valeur oid de votre table spatiale.

4.7.3. Utilisation avancée

Révision SVN ( 8935 )

La clause USING pseudo-SQL est utilisée pour ajouter des informations pour aider mapserver à comprendre les résultats de requêtes plus complexes. Plus précisément, lorsqu'une vue ou une sous-sélection est utilisée comme table source (la chose à droite de "FROM" dans une définition DATA), il est plus difficile pour mapserver de déterminer automatiquement un identifiant unique pour chaque ligne ainsi que le SRID pour le tableau. La clause USING peut fournir à mapserver ces deux informations comme suit :

Mapserver requiert un identifiant unique pour chaque ligne afin d'identifier la ligne lors des requêtes cartographiques. Normalement, il utiliserait l'oid comme identifiant unique, mais les vues et les sous-sélections n'ont pas automatiquement de colonne oid. Si vous souhaitez utiliser la fonctionnalité de requête de Mapserver, vous devez ajouter une colonne unique à votre vue ou sous-sélectionner et la déclarer avec USING UNIQUE . Par exemple, vous pouvez sélectionner explicitement l'une des valeurs oid de la table à cet effet, ou toute autre colonne qui est garantie d'être unique pour l'ensemble de résultats.

L'instruction USING peut également être utile même pour les instructions DATA simples, si vous effectuez des requêtes de carte. Il était auparavant recommandé d'ajouter un index sur la colonne oid des tables utilisées dans les couches interrogeables, afin d'accélérer les performances des requêtes cartographiques. Cependant, avec la clause USING, il est possible de dire à mapserver d'utiliser la clé primaire de votre table comme identifiant pour les requêtes de carte, et il n'est alors plus nécessaire d'avoir un index supplémentaire.

"Interroger une carte" est l'action de cliquer sur une carte pour demander des informations sur les caractéristiques de la carte à cet endroit. Ne confondez pas les "requêtes de mappage" avec la requête SQL dans une définition DATA.

PostGIS a besoin de savoir quel système de référencement spatial est utilisé par les géométries afin de renvoyer les données correctes à mapserver. Normalement, il est possible de trouver ces informations dans la table "geometry_columns" de la base de données PostGIS, cependant, cela n'est pas possible pour les tables créées à la volée telles que les sous-sélections et les vues. Ainsi, l'option USING SRID= permet de spécifier le SRID correct dans la définition DATA.

Avertissement

L'analyseur pour les couches Mapserver PostGIS est assez primitif et est sensible à la casse dans quelques domaines. Veillez à ce que tous les mots-clés SQL et toutes vos clauses USING soient en majuscules, et que votre clause USING UNIQUE précède votre clause USING SRID.


4 réponses 4

Dans la plupart des cas, l'ordre de tri d'un index n'est guère pertinent. Postgres peut numériser vers l'arrière pratiquement aussi rapidement. Mais pour les requêtes de plage sur plusieurs colonnes, cela peut faire un énorme différence. Étroitement liés:

L'ordre de tri de la première colonne id_phi dans l'index n'a pas d'importance. Depuis qu'il est vérifié égalité ( = ), il devrait venir en premier. Tu as totalement raison. Plus dans cette réponse connexe:

Postgres peut sauter à id_phi = 0 en un rien de temps et considérer les deux colonnes suivantes de l'index correspondant. Ceux-ci sont interrogés avec conditions de plage de l'ordre de tri inversé ( <= , >= ). Dans mon index, les lignes de qualification viennent en premier. Devrait être le moyen le plus rapide possible avec un index B-Tree 1 :

  • Vous voulez start_date_time <= quelque chose : l'index a d'abord l'horodatage le plus ancien.
  • S'il est admissible, vérifiez également la colonne 3.
    Récursive jusqu'à ce que la première ligne ne se qualifie pas (super rapide).
  • Vous voulez end_date_time >= quelque chose : l'index a d'abord le dernier horodatage.
  • S'il se qualifie, continuez à récupérer les lignes jusqu'à ce que le premier ne le fasse pas (super rapide).
    Continuez avec la valeur suivante pour la colonne 2 ..

Postgres peut soit numériser vers l'avant ou alors en arrière. La façon dont vous aviez l'index, il doit lire tout lignes correspondant sur les deux premières colonnes puis filtre Au troisieme. Assurez-vous de lire le chapitre Index et ORDRE PAR dans le manuel. Cela correspond assez bien à ta question.

Combien de lignes correspondent sur les deux premières colonnes ?
Seuls quelques-uns avec un start_date_time proche du début de la plage horaire de la table. Mais presque toutes lignes avec id_phi = 0 à la fin chronologique du tableau ! Les performances se détériorent donc avec des heures de démarrage plus tardives.

Estimations du planificateur

Le planificateur estime rows=62682 pour votre exemple de requête. Parmi ceux-ci, aucun ne se qualifie ( rows=0 ). Vous pourriez obtenir de meilleures estimations si vous augmentez la cible des statistiques pour la table. Pour 2.000.000 lignes .

. pourrait payer. Ou même plus haut. Plus dans cette réponse connexe:

Je suppose que vous n'en avez pas besoin pour id_phi (seulement quelques valeurs distinctes, uniformément réparties), mais pour les horodatages (beaucoup de valeurs distinctes, inégalement réparties).
Je ne pense pas non plus que cela ait beaucoup d'importance avec l'indice amélioré.

CLUSTER / pg_repack / pg_squeeze

Si vous le souhaitez encore plus rapidement, vous pouvez rationaliser l'ordre physique des lignes de votre table. Si vous pouvez vous permettre de verrouiller votre table exclusivement (en dehors des heures d'ouverture par exemple), réécrivez votre table et ordonnez les lignes selon l'index avec CLUSTER :

Ou considérez pg_repack ou le dernier pg_squeeze, qui peut faire la même chose sans verrou exclusif sur la table.

Dans tous les cas, l'effet est que moins de blocs doivent être lus dans la table et que tout est pré-trié. Il s'agit d'un effet ponctuel qui se détériore avec le temps, les écritures sur la table fragmentant l'ordre de tri physique.


Travailler avec des données LiDAR dans PostgreSQL

Les correctifs sont un moyen efficace de stocker des données de nuage de points dans des tables PostgreSQL, où chaque correctif se compose d'un certain nombre de points proches. Cela permet de réduire considérablement le nombre de lignes pour les données et d'accélérer les opérations sur la table. Le schéma des patchs et des points est décrit dans la table pointcloud_formats de la base de données. De plus, l'extension de nuage de points offre des fonctionnalités pour travailler à la fois avec des points et des patchs, y compris l'extraction d'attributs individuels tels que les altitudes, l'intensité ou les coordonnées pour chaque point ou l'étendue des zones de délimitation des patchs. Si vous souhaitez en savoir plus, cette présentation de Paul Ramsey à OpenGEO contient de très bonnes explications avec des exemples de points et de correctifs.

Toutes les étapes suivantes sont implémentées dans le conteneur pgpointcloud, que vous pouvez utiliser à l'aide des commandes suivantes :

Nous joindrons les données LiDAR aux empreintes des bâtiments OSM pour extraire les élévations et les hauteurs des bâtiments. Donc, commençons par charger les données du fichier de formes OSM décrites ci-dessus, dans une table appelée osm à l'aide de la commande suivante à partir du conteneur pgpointcloud-

Nous utiliserons des requêtes PostgreSQL pour la plupart des étapes restantes. Vous pouvez accéder à la base de données à l'aide de la commande suivante et exécuter toutes les requêtes à partir de la base de données.

Nous sommes maintenant prêts à extraire les élévations et les hauteurs des bâtiments et le processus détaillé est décrit ci-dessous. Le processus d'aperçu pour obtenir les élévations du bâtiment consiste d'abord à identifier tous les points LiDAR dans les emprises du bâtiment à partir d'OSM, puis à obtenir l'élévation du bâtiment à partir de cette collection de points. La vue d'ensemble pour obtenir les hauteurs de bâtiment consiste d'abord à estimer l'élévation du sol le long du contour de chaque empreinte de bâtiment et à la soustraire de l'élévation du bâtiment.

  1. Pré-traiter la table lidar pour créer un index spatial.
    une. Affichez les attributs de chaque point d'un patch. Les formats des patchs et des points sont décrits au format XML dans un tableau séparé appelé pointcloud_formats .
    SELECT * FROM pointcloud_formats
    b. Affichez la liste des colonnes du tableau. Vous remarquerez que les patchs sont stockés dans la colonne pa .
    d lidar
    c. Afficher le nombre de patchs et le nombre total de points dans le tableau —
    SELECT Count(*), Sum(PC_NumPoints(pa)) FROM lidar
    ré. Créez un index spatial sur la table lidar pour des requêtes plus rapides (accélération observée de 40 fois) —
    CREATE INDEX lidar_envelope_pkey ON lidar USING GIST(PC_EnvelopeGeometry(pa))
  2. Pré-traiter la table osm pour créer un index spatial.
    une. Les points en dehors d'une zone de délimitation de la ville peuvent être supprimés à l'aide de la requête ci-dessous. Remplacez ( lng_min, lat_min, lng_max, lat_max) par l'étendue de la zone de délimitation d'intérêt, où lng fait référence à la longitude et lat à la latitude.
    DELETE FROM osm WHERE osm.gid IN (SELECT a.gid FROM osm a WHERE NOT ST_Intersects(a.geom, ST_MakeEnvelope (lng_min, lat_min, lng_max, lat_max, 4326)) )
    b. Je recommande de convertir la géométrie de la colonne osm geomtery vers le même SRID (système de référence spatial) de la table lidar. Les zones UTM sont idéales pour traiter une petite zone et des mesures, nous convertissons donc geom en EPSG:26910, qui est la même référence pour les données LiDAR.
    ALTER TABLE osm ALTER COLUMN geom TYPE geometry(MultiPolygon, 26910) USING ST_Transform(geom, 26910)
    CRÉER UN INDEX osm_26910_pkey SUR osm EN UTILISANT GIST (geom)
  3. Créez une nouvelle colonne dans osm pour contenir des parcelles de points qui se trouvent dans chacune des emprises du bâtiment.
    une. Afin d'ajouter une nouvelle colonne à osm pour stocker les correctifs, vérifiez d'abord le type de correctifs dans lidar à l'aide de d lidar . Typiquement, ce serait pcpatch(1) . Ajouter une nouvelle colonne à osm pour stocker les correctifs
    ALTER TABLE osm ADD COLUMN pa pcpatch(1)
    b. Stockez le patch de nuage de points qui recouvre chaque polygone d'empreinte de bâtiment. La requête ci-dessous identifie d'abord tous les patchs qui coupent chaque empreinte de bâtiment, puis calcule leur union et trouve enfin l'intersection du patch d'union avec l'empreinte. Attendez-vous à ce que cette requête prenne quelques heures.
    UPDATE osm SET pa = sq.pa FROM (AVEC patchs AS (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa FROM lidar AS l JOIN osm AS o ON PC_INTERSECTS(l.pa, o.geom )) SELECT gid, PC_INTERSECTION(PC_UNION(pa), geom) AS pa FROM patchs GROUP BY gid, geom) AS sq WHERE osm.gid = sq.gid
  4. Nous décrirons l'élévation du bâtiment comme une statistique pour les élévations de tous les points dans l'empreinte du bâtiment. La statistique peut être choisie en fonction de votre cas d'utilisation. Ici, je calcule les altitudes maximale, moyenne et médiane de tous les points. Notez que la valeur Z dans les données LiDAR est l'altitude donnée par une certaine donnée spécifiée dans ses métadonnées (NAD83 dans ce cas). Au lieu de prendre les maxima directement, il est estimé comme la valeur du 99,9e centile pour réduire la possibilité d'une valeur aberrante entraînant une surestimation de l'altitude la plus élevée.
    une. Ajouter des colonnes pertinentes à osm —
    ALTER TABLE osm AJOUTER COLONNE z_avg DOUBLE PRECISION NULL, AJOUTER COLONNE z_median DOUBLE PRECISION NULL, AJOUTER COLONNE z_max DOUBLE PRECISION NULL
    b. Calculer et stocker les statistiques d'altitude
    MISE À JOUR osm SET z_avg = sq.z_avg, z_median = sq.z_median, z_max = sq.z_max FROM (AVEC patchs AS (SELECT o.gid AS gid, PC_GET(PC_EXPLODE(o.pa), 'Z') AS pt_z FROM osm AS o) SELECT gid, AVG(pt_z) AS z_avg, PERCENTILE_CONT(0.5) DANS LE GROUPE(ORDER BY pt_z) AS z_median, PERCENTILE_CONT(0.999) DANS LE GROUP(ORDER BY pt_z) AS z_max FROM patchs GROUP BY gid) AS sq WHERE osm .gid = sq.gid
  5. Obtenez l'élévation du sol en identifiant le contour du bâtiment et en le joignant aux données LiDAR.
    une. Créez une nouvelle table appelée osm_outline en tant que copie de la table osm pour traiter le contour du bâtiment généré en prenant la différence entre une zone tampon de 2 m et 1 m autour des polygones du bâtiment.
    CREATE TABLE osm_outline AS SELECT gid, osm_id, geom FROM osm
    MISE À JOUR osm_outline SET geom = buffer FROM (SELECT o.gid, ST_MULTI(ST_DIFFERENCE(ST_MULTI(ST_Buffer(o.geom, 2)), ST_MULTI(ST_BUFFER(o.geom, 1)))) AS buffer FROM osm_outline AS o) AS sq O osm_outline.gid = sq.gid
    CRÉER UN INDEX osm_outline_pkey ON osm_outline EN UTILISANT GIST (geom)
    b. Stockez les patchs de nuage de points qui croisent le contour. La requête ci-dessous identifie d'abord tous les patchs qui coupent chaque contour, puis calcule leur union et trouve enfin l'intersection du patch d'union avec le contour. Attendez-vous à ce que cette requête prenne quelques heures.
    MODIFIER LA TABLE osm_outline AJOUTER LA COLONNE pa pcpatch(1)
    UPDATE osm_outline SET pa = sq.pa FROM (AVEC patchs AS (SELECT o.gid AS gid, o.geom AS geom, l.pa AS pa FROM lidar AS l JOIN osm_outline AS o ON PC_INTERSECTS(l.pa, o.geom )) SELECT gid, PC_INTERSECTION(PC_UNION(pa), geom) AS pa FROM patchs GROUP BY gid, geom) AS sq WHERE osm_outline.gid = sq.gid
    c. Stockez la valeur Z minimale du nuage de points qui coupe les contours du bâtiment. Au lieu de prendre les minima directement, il est estimé comme la valeur du 1er centile pour réduire la possibilité d'une valeur aberrante entraînant une sous-estimation de l'élévation du sol.
    ALTER TABLE osm_outline ADD COLUMN z_ground DOUBLE PRECISION NULL
    MISE À JOUR osm_outline SET z_ground = sq.z_min FROM (AVEC les correctifs AS (SELECT o.gid AS gid, PC_GET(PC_EXPLODE(o.pa), 'Z') AS pt_z FROM osm_outline AS o) SELECT gid, PERCENTILE_CONT(0.01) DANS LE GROUPE (ORDER BY pt_z) AS z_min FROM patchs GROUP BY gid) AS sq WHERE osm_outline.gid = sq.gid
    ré. Ajoutez l'élévation du sol à la table osm d'origine et calculez les hauteurs comme la différence entre les élévations d'empreinte et les élévations du sol.
    ALTER TABLE osm ADD COLUMN z_ground DOUBLE PRECISION NULL, ADD COLUMN height_avg DOUBLE PRECISION NULL, ADD COLUMN height_median DOUBLE PRECISION NULL, ADD COLUMN height_max DOUBLE PRECISION NULL
    UPDATE osm AS o SET z_ground = oo.z_ground FROM osm_outline oo WHERE o.gid = oo.gid
    MISE À JOUR osm SET height_avg = (z_avg — z_ground), height_median = (z_median — z_ground), height_max = (z_max — z_ground)

C'est ça! You have successfully calculated the building elevations, ground elevations, and building heights for each building and all this data is stored in the osm table along with the building footprint geometry for each building.

If you would like to visualize the elevations and heights, you can export your data to a Shapefile. First exit the database using q , and then run the following command to export the osm table to the file named sanfrancisco_buildings.shp —

I imported the Shaepefile into QGIS, and used the Qgis2threejs plugin to create an interactive output as shown at the top of this article. The plugin allows showing both ground elevations and buildings in 3D, and the visualization can be accessed easily from any browser.

Interested in learning more about our technology? Head over to our site Internet or our Medium page to read about some of our recent projects!


Voir la vidéo: PostGIS Lesson 6 - Using PostgreSQL with Jupyter Notebook (Octobre 2021).