PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 Re : Général » Optimisation du calcul du nombre de points dans une surface projetée » 22/02/2012 17:33:09

merci pour précisions que je vais étudier

les projections ne sont pas pré-définies
la surface peut varier d'une région à la planète entière

dans le cas de la projection sphérique Mercator il est je semble à priori possible
de travailler directement sur l'expression en degré des limites de la surface projetée
ce qui correspond à une requête ayant la syntaxe suivante :

select count(*) from table where coord && ST_
Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(xmin,ymin), ST_Point(xmax, ymax)),900913), 4326) and date ...

ce qui nous permet d'avoir un traitement plus rapide et d'autant plus intéressant que plus la surface
est grande plus il y a de points

Jean-Marie

#2 Re : Général » Optimisation du calcul du nombre de points dans une surface projetée » 21/02/2012 11:28:27

Bonjour

effectivement il n'y a pas d'index utilisé ;
mais créer un index sur le st_transform ne me parait pas envisageable car nous sommes amenés à utiliser différentes projections

l'évaluation du résultat se fait en comparant le nombre de points obtenu par la requête à postgres avec l'affichage de ces points réalisé au moyen d'une requête WMS à mapserver

dans le cas de la requête 'longue" où chaque point est converti en degré, il y a bien correspondance

pour revenir à la requête plus rapide mais fausse, je remarque que la conversion en degré des limites projetés
renvoie 5 points (voir ci-dessous la description du polygone) ; il serait intéressant d'obtenir un polygone comportant davantage de points afin d'obtenir une meilleure précision) ; mais je ne sais pas comment faire

SELECT ST_AsText(ST_Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-111740.67231994, 452481.05159311), ST_Point(1763272.204062, 1722480.3657935)),27572), 4326)) As wgs_geom;

wgs_geom                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
POLYGON((-4.86421028855627 30.9545265233267,-6.2658910764966 42.1561991488799,16.3040533297558 41.5763321847958,14.0513693532454 30.482942611268,-4.86421028855627 30.9545265233267))

#3 Re : Général » Optimisation du calcul du nombre de points dans une surface projetée » 20/02/2012 16:45:12

Ce n'est pas envisageable car nous pouvons être amené à utiliser différentes projections selon la région et le pays concerné

Jean-Marie

#4 Général » Optimisation du calcul du nombre de points dans une surface projetée » 20/02/2012 15:39:29

Jean-Marie
Réponses : 8

Bonjour

je cherche à optimiser une requête calculant le nombre de points dans une surface projetée ;
les coordonnées des points dans la base de données sont stockées en degré
mais les limites de la surface sont spécifiées en projeté (exemple LambertIIe)

une première solution est de convertir en projeté chaque point et de tester s'il appartient à la surface en projeté (délimitée par 4 points) :

select count(*) from table where  st_transform
(coord,27572) && st_setsrid('BOX(-111740.67231994 452481.05159311, 1763272.204062 1722480.3657935)'::box2d,27572)

cette solution donne un résultat juste mais le temps de traitement augmente très vite avec le nombre de points

pour réduire cette durée j'ai pensé convertir en degré le rectangle de manière à éviter la conversion sur les points ; je constate qu'effectivement la requête est plus rapide mais le résultat est faux

select count(*) from table where coord && ST_
Transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-111740.67231994, 452481.05159311), ST_Point(1763272.204062, 1722480.3657935)),27572), 4326)

j'en déduis que la transformation des points projetés en points en degrés ne délimite pas la même surface

est-ce vraiment impossible de déterminer une surface en degré qui soit équivalente à la surface projetée ?
si c'est vraiment le cas, y-a-t'il un autre moyen pour optimiser le temps de traitement ?

merci d'avance

Jean-Marie

#5 Re : Général » prendre en compte un time zone spécifique » 01/02/2012 11:42:56

Bonjour

à priori la solution la plus intéressante est d'utiliser la syntaxe que vous avez mentionné

select ((date at time zone 'UTC') at time zone 'TIMEZONE')

merci pour votre précieuse contribution

Jean-Marie

#6 Re : Général » prendre en compte un time zone spécifique » 30/01/2012 16:13:53

le chanp date est un timestamp sans time zone :

Column  |            Type                         | Modifiers
--------------------------------------------------------------
date       | timestamp without time zone | not null
...

#7 Re : Général » prendre en compte un time zone spécifique » 26/01/2012 19:08:23

merci pour ces indications

mais apparemment j'ai le souci suivant :
quand j'utilise le time zone CET ou "Europe/Paris" j'obtiens 1 heure de - qu'en UTC (au lieu d'1 heure de plus)

ainsi

SELECT to_char(date at time zone 'CET','dd/mm/YYYY HH24:MI:SS'), to_char(x(coord),'FM990D999'), to_char(y(coord),'FM990D999') ...where date between '2012-01-26 12:15:18' and '2012-01-26 14:15:18'...

me donne

26/01/2012 12:39:15 | 13.485  | 52.276

dans le cas où je remplace CET par UTC+1, j'obtiens pourtant le résultat attendu soit :

26/01/2012 14:39:15 | 13.485  | 52.276

je précise que mes dates de début et de fin '2012-01-26 12:15:18' et '2012-01-26 14:15:18' sont en UTC

peut-être le problème vient-il de là

vous avez dit qu'il faut que le temps de départ ait un time zone

comment puis-je préciser cette information

merci d'avance

Jean-Marie

#8 Général » prendre en compte un time zone spécifique » 26/01/2012 17:53:27

Jean-Marie
Réponses : 9

Bonjour

je fais actuellement des requêtes sur des dates en UTC
je souhaiterais pouvoir spécifier un time zone différent au niveau du format de sortie

par exemple j'ai une requête du genre :

select to_char(date,'dd/mm/YYYY HH24:MI:SS'), to_char(x(coord),'FM990D999'), to_char(y(coord),'FM990D999') ...where date between '2012-01-26 12:15:18' and '2012-01-26 14:15:18'...

j'obtiens :
26/01/2012 13:39:15 | 13.485  | 52.276

le time zone est par défaut en UTC

je voudrais pouvoir afficher cette date dans un time zone différent de UTC (exemple CET) ce qui donnerait

26/01/2012 14:39:15 | 13.485  | 52.276

quelle la manière la plus simple de m'y prendre sachant que le time zone est fourni par une requête http
et varie suivant le pays d'où provient cette requête

merci beaucoup

Jean-Marie

#10 Re : Général » passer du type timestamp à un entier » 14/06/2011 17:43:40

ok merci bien

la commande devient ainsi :
select date from table where extract(epoch from date) > 1308030000 and extract(epoch from date) < 1308045518;

#11 Général » passer du type timestamp à un entier » 14/06/2011 15:45:46

Jean-Marie
Réponses : 6

Bonjour

j'ai des enregistrements chacun ayant un champ date de type timestamp dans le format yyyy-mm-dd hh:mm:ss ;
je voudrais faire des requêtes sur ce champ mais en me basant sur un entier correspondant au nombre de secondes depuis le 01/01/1970

donc par exemple au lieu d'écrire

select date, x(coord), y(coord) from table where date between '2011-06-14 05:40:00' and '2011-06-14 09:58:38';

je voudrais arriver à une requête du genre

select date, x(coord), y(coord) from table where (date > 1308030000 and date < 1308045518);

comment puis-je m'y prendre ?

merci d'avance

Jean-Marie

#13 Re : Général » Requêtes par rapport à la date d'insertion des enregistrements » 17/03/2011 10:36:38

Indexes:
    "stroke_coord_key" gist (coord)
    "stroke_date_key" btree (date)
    "stroke_num_key" btree (num)
Check constraints:
    "enforce_dims_coord" CHECK (ndims(coord) = 2)
    "enforce_geotype_coord" CHECK (geometrytype(coord) = 'POINT'::text OR coord IS NULL)
    "enforce_srid_coord" CHECK (srid(coord) = 4326)

#14 Re : Général » Requêtes par rapport à la date d'insertion des enregistrements » 16/03/2011 19:28:45

désolé je n'avais pas capté

voici le résultat :

EXPLAIN ANALYZE select date, x(coord), y(coord) from ldb.stroke where insertdate between '2011-03-15 09:37:00.679191' and '2011-03-15 09:37:05.073541' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);
                                                                                                                                                                                            QUERY PLAN                                                                                                                                                                                             
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on stroke  (cost=0.00..1528695.10 rows=1 width=33) (actual time=681410.009..684291.580 rows=5 loops=1)
   Filter: ((insertdate >= '2011-03-15 09:37:00.679191'::timestamp without time zone) AND (insertdate <= '2011-03-15 09:37:05.073541'::timestamp without time zone) AND (coord && '0103000020E6100000010000000500000000000000008066C000000000008056C000000000008066
C0000000000080564000000000008066400000000000805640000000000080664000000000008056
C000000000008066C000000000008056C0'::geometry))
Total runtime: 684306.400 ms
(3 rows)

#15 Re : Général » Requêtes par rapport à la date d'insertion des enregistrements » 16/03/2011 16:50:39

ah j'ai oublié la version :
PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu

#16 Re : Général » Requêtes par rapport à la date d'insertion des enregistrements » 16/03/2011 16:47:58

après analyse :
la requête a mis environ 11 minutes pour s'exécuter
il y a environ 34 000 000 enregistrements
la table a une taille de 15 GB

#17 Général » Requêtes par rapport à la date d'insertion des enregistrements » 16/03/2011 13:22:03

Jean-Marie
Réponses : 9

Bonjour

je souhaite faire des requêtes sur la date d'insertion des enregistrements
mais je m'aperçois que la requête ne se termine pas

si je prends l'exemple suivant basé sur le champ date de la donnée (et non pas date d'insertion) :

select date, x(coord), y(coord), insertdate from table where date between '2011-03-15 09:35:30' and '2011-03-15 9:35:40' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);

j'obtiens bien un résultat immédiat :

          date           |    x     |    y     |         insertdate         
-------------------------+----------+----------+----------------------------
2011-03-15 09:35:34.974 | -57.4305 |  -2.4284 | 2011-03-15 09:37:00.679191
2011-03-15 09:35:36.755 | -36.9402 | -21.7172 | 2011-03-15 09:37:02.429322
2011-03-15 09:35:37.27  | -37.2161 | -21.6957 | 2011-03-15 09:37:02.743891
2011-03-15 09:35:39.295 | -14.6501 |   0.0889 | 2011-03-15 09:37:04.958068
2011-03-15 09:35:39.366 | -14.6871 |   0.1062 | 2011-03-15 09:37:05.073541
(5 rows)

si maintenant j'essaie sur la date d'insertion :

select date, x(coord), y(coord) from table where insertdate between '2011-03-15 09:37:00.679191' and '2011-03-15 09:37:05.073541' and coord && st_setsrid('BOX(-180 -90, 180 90)'::box2d,4326);

la requête bloque indéfiniment et je suis obligé de l'interrompre

quelqu'un sait-il comment je peux résoudre ce problème ?

merci d'avance

Jean-Marie

#19 Re : Général » Comment exprimer dans une requête des dates en millisecondes » 15/03/2011 18:30:24

effectivement il s'agit bien toujours de l'heure UTC et non de l'heure locale

par contre je pensais pouvoir faire une seule requête avec les dates de début et de fin avec ce format entier

dans la solution que vous proposez faut-il faire 3 requêtes ?

une pour la date de début
SELECT '1970-01-01 00:00:00'::timestamp_begindate + '1 ms'::interval * 1000000000000;
      ?column?

une pour la date de fin
SELECT '1970-01-01 00:00:00'::timestamp_enddate + '1 ms'::interval * 1000000000000;
      ?column?

et une 3ème pour sélectionner mes données comprises entre les dates de début et de fin

comment combinez vous ces 3 requêtes ?

merci d'avance

#20 Re : Général » Comment exprimer dans une requête des dates en millisecondes » 15/03/2011 18:02:44

depuis le 1er janvier 1970
j'ai cette information pour la date de début et la date de fin ;
est-il alors possible de faire les requêtes avec des valeurs de ce type (donc entier long)
et non pas avec des chaines de caractères

#21 Re : Général » Comment exprimer dans une requête des dates en millisecondes » 15/03/2011 17:43:47

Merci pour cette information
mais je voudrais exprimer les dates sous forme d'entier long

est-ce possible ?

merci bien

jean-Marie

#22 Général » Comment exprimer dans une requête des dates en millisecondes » 15/03/2011 17:16:03

Jean-Marie
Réponses : 8

Bonjour

je fais actuellement des requêtes du type :
select date, x(coord), y(coord) from table where date between '2011-3-15 11:28:7' and '2011-3-15 11:23:7' ;

comment dois-je faire pour exprimer des dates en millisecondes comme par exemple :
select date, x(coord), y(coord) from table where date between '130020176123' and '1300201992425' ;

merci d'avance

Jean-Marie

Pied de page des forums

Propulsé par FluxBB