By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,261 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Conversion of MySQL spatial query with intermediate tables to PostGre spatial query

P: 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
Jan 24 '08 #1
Share this Question
Share on Google+
2 Replies


P: 1
However, when I run this query execution takes such a long time that I have to kill the query. I see no syntax errors.
Do you use spatial indexes?
You can run EXPLAIN SELECT count(*) FROM... to see what is going on, and where is the performance problem.

Hope this help
Regards
Mariano
Feb 6 '08 #2

P: 2
Do you use spatial indexes?
You can run EXPLAIN SELECT count(*) FROM... to see what is going on, and where is the performance problem.

Hope this help
Regards
Mariano
Yes, I did already use spatial indices. Running EXPLAIN SELECT did confirm my hunch that the straight joins (done with the comma sign) and the left joins take up most of the time and spoil the query performance. I know that the PostGRESQL query optimizer can handle linear joins but it doesn't seem to be able to cope that well with joins between pairs of composite relations. Any idea how to work best with joins between pairs of composite relations in PostGRESQL ?

Regards, Diederick
Feb 12 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.