473,320 Members | 1,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
2 4408
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
dvelst
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

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

Similar topics

0
by: Gijsbert Noordam | last post by:
------_=_NextPart_001_01C349DA.E4B7E120 Content-Type: text/plain; charset="iso-8859-1" Hi, As a newcomer to this mailing list -- and to the MySQL database environment -- my main field of...
4
by: David | last post by:
Hi, I have copied some tables from my MS Access 2000 ap into our Cobalt Qube 3 server running MySQL. I now wish to convert a query from Access into MySQL but I just cannot get it working: ...
2
by: Belinda | last post by:
Hi. I am just getting started with DB2's spatial extender and could really use some help. Pointers to good docs or examples are welcome. I am using DB2 version 8 on Sun. I have a database of...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
39
by: windandwaves | last post by:
Hi Folk I have to store up to eight boolean bits of information about an item in my database. e.g. with restaurant drive-through facility yellow windows
2
by: bwana.mpoa | last post by:
Hi, We're using a mySQL database as a replica of another (Sybase) DB for reporting purposes. The Sybase is part of a real-time mission critical system - hence the separate database where people...
1
by: webandwe | last post by:
Hi The code below I took from a postgre file and convert it with a program to a mysql. Once i tried to import it to mysql it gave me an error and when i past in a php file to upload it trough a...
6
by: bill | last post by:
I am about to start on a module that will accept a location from a user, use Google geolocation services to get the lat/lon and then compute the distance from the site visitor to about 100 kennels...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.