2
Hi All,
I have the following query which was based on a working query in MySQL:
(don't be afraid of the size of the query you see below, later on I will concentrate on part of this query)
SELECT count(*) FROM ((SELECT * FROM tileinfo where julianDay BETWEEN DATE2JDAY(('2003-03-01 00:00:00'),0) AND DATE2JDAY(('2003-03-01 23:59:59' ),0) AND intersects(tile, geomFromText('POLYGON((-10.84 35.8,18.14 35.8,18.14 59.41,-10.84 59.41,-10.84 35.8))',4326))) tic0 LEFT JOIN (tileinfo_meta__2p left join meta__2p on tileinfo_meta__2p.fk_name = meta__2p.pk_name) on tic0.pk_tileinfo = tileinfo_meta__2p.fk_tileinfo ),
((SELECT * FROM tileinfo where julianDay BETWEEN DATE2JDAY(('2003-03-01 00:00:00'),0) AND DATE2JDAY(('2003-03-01 23:59:59' ),0) AND intersects(tile, geomFromText('POLYGON((-10.84 35.8,18.14 35.8,18.14 59.41,-10.84 59.41,-10.84 35.8))',4326))) tib1 LEFT JOIN gdp__2p ON tib1.pk_tileinfo = gdp__2p.fk_tileinfo)
where tic0.pixelNumber BETWEEN 500 AND 3000 AND tic0.sunZenithAngle BETWEEN 0.0 AND 100.0 AND tic0.relAzimuthAngle BETWEEN 0.0 AND 360.0 AND absOrbit BETWEEN 1 AND 99999 AND o3_gdp BETWEEN 500.0 AND 1000.0 AND tib1.pixelNumber BETWEEN 500 AND 3000 AND tib1.sunZenithAngle BETWEEN 0.0 AND 100.0 AND tib1.relAzimuthAngle BETWEEN 0.0 AND 360.0;
As you see this query already entails conversions to PostGre and PostGIS because I use single quotes i.s.o. double quotes, I have a SRID (that is 4326) etc. However, when I run this query execution takes such a long time that I have to kill the query. I see no syntax errors.
When concentrating on part of this query I get a syntax error though:
I took
(SELECT * FROM tileinfo where julianDay BETWEEN DATE2JDAY(('2003-03-01 00:00:00'),0) AND DATE2JDAY(('2003-03-01 23:59:59' ),0) AND intersects(tile, geomFromText('POLYGON((-10.84 35.8,18.14 35.8,18.14 59.41,-10.84 59.41,-10.84 35.8))',4326))) tic0
LEFT JOIN (tileinfo_meta__2p left join meta__2p on tileinfo_meta__2p.fk_name = meta__2p.pk_name) on tic0.pk_tileinfo = tileinfo_meta__2p.fk_tileinfo );
Execution yields the error
ERROR: syntax error at or near "tic0"
LINE 1: ...8,18.14 59.41,-10.84 59.41,-10.84 35.8))',4326))) tic0 LEFT ...
tic0 is an intermediate table here
I studied intermediate/temporary tables in PostGreSQL and then I started experimenting with my foundings. I executed the following 2 queries:
CREATE TEMP TABLE tia0 AS SELECT * FROM tileinfo where julianDay BETWEEN DATE2JDAY(('2003-05-28 00:00:00'),0) AND DATE2JDAY(('2003-05-28 23:59:59'),0) AND intersects(tile, geomFromText('POLYGON((-180.0 90.0,179.99 90.0,179.99 -90.0,-180.0 -90.0,-180.0 90.0))',4326))
and
select * from tia0 left join (tileinfo_meta__2p LEFT JOIN meta__2p on tileinfo_meta__2p.fk_name = meta__2p.pk_name) on tia0.pk_tileinfo = tileinfo_meta__2p.fk_tileinfo;
Both ran well. Is this the way it should be handled in PostGre ? Actually I want to use intermediate tables like I used them in MySQL such that I can have a big query i.s.o. running several queries sequentially. Can you give me a conversion of the big query above ?
I use PostGreSQL 8.2.5 and PostGIS 1.2.1. I work on SuSe 10.1.
Thanks in advance, Diederick
