473,375 Members | 1,222 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,375 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 4409
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...
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...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.