tune a geometric query
hi,
we are working on automotive traking solutions and insert our location records
into the mysql database v 4.1.20. If we want search for a particular record,
its taking too long time and mysql is identifying the queries as slow queries.
I have attached the queries.
How to optimize the attache query ?
regards
shann
___
Hot new product - Spider Networks introduces stunning online ePortfolio
solution for students and teachers
http://www.spider-networks.net/solutions/eportfolio.html
select A.name,A.district,x(GeomFromText(AsText(A.geo))) as
x,y(GeomFromText(AsText(A.geo))) as
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
11.83884)')) as Distance FROM (select geo,name,district from
cities_point union all select geo,name,district from cities_font_point
union all select geo,name,district from State_Highways_point union all
select geo,name,district from Other_Roads_point union all select
geo,name,district from Major_Roads_point union all select
geo,name,district from Vet_Clinics_point union all select
geo,name,district from University_point union all select
geo,name,district from Tourist_Info_point union all select
geo,name,district from Temples_point union all select geo,name,district
from Taxi_Stands_point union all select geo,name,district from
Stadiums_point union all select geo,name,district from
Sports_Clubs_point union all select geo,name,district from
Shops_WhiteGds_point union all select geo,name,district from
Shops_Sports_point union all select geo,name,district from
Shops_RealEstate_point union all select geo,name,district from
Shops_Music_point union all select geo,name,district from
Shops_Misc_point union all select geo,name,district from Shops_LPG_point
union all select geo,name,district from Shops_Jewellery_point union all
select geo,name,district from Shops_Furnt_point union all select
geo,name,district from Shops_Footwear_point union all select
geo,name,district from Shops_Computer_point union all select
geo,name,district from Shops_Chemists_point union all select
geo,name,district from Shops_Bakery_point union all select
geo,name,district from Shops_Apparel_point union all select
geo,name,district from Shopping_Ctrs_point union all select
geo,name,district from Services_Travel_point union all select
geo,name,district from Services_Professional_point union all select
geo,name,district from Services_Financial_point union all select
geo,name,district from Service_Stations_point union all select
geo,name,district from Schools_point union all select geo,name,district
from Restaurants_point union all select geo,name,district from
Religious_Pls_Oth_point union all select geo,name,district from
Railway_Stations_point union all select geo,name,district from
Railway_Reservations_point union all select geo,name,district from
PreSchools_point union all select geo,name,district from
PostOffices_point union all select geo,name,district from
PoliceStations_point union all select geo,name,district from
PoliceChaukis_point union all select geo,name,district from
PetrolPumps_point union all select geo,name,district from Parks_point
union all select geo,name,district from OtherInstt_point union all
select geo,name,district from Offices_point union all select
geo,name,district from Office_Airlines_point union all select
geo,name,district from Museums_point union all select geo,name,district
from Mosques_point union all select geo,name,district from Misc_point
union all select geo,name,district from Libraries_point union all select
geo,name,district from LevelCrossing_point union all select
geo,name,district from Industries_point union all select
geo,name,district from Hotels_point union all select geo,name,district
from Hostels_point union all select geo,name,district from
Hospitals_point union all select geo,name,district from
Historical_Pls_point union all select geo,name,district from
Gymnasiums_point union all select geo,name,district from Gurdwaras_point
union all select geo,name,district from Graveyards_point union all
select geo,name,district from GovtOffices_point union all select
geo,name,district from Dispensaries_point union all select
geo,name,district from Discotheques_point union all select
geo,name,district from Diagnostic_Ctrs_point union all select
geo,name,district from CyberCafes_point union all select
geo,name,district from Cultural_Centres_point union all select
geo,name,district from Crematory_point union all select
geo,name,district from Couriers_point union all select geo,name,district
from Computer_Instt_point union all select geo,name,district from
Colleges_point union all select geo,name,district from Clubs_point union
all select geo,name,district from Clinics_point union all select
geo,name,district from Cinemas_point union all select geo,name,district
from Church_point union all select
optimize mysql geometric query
hi,
i have attached my geometric query which is very slow. how to optimize this
query
regards
shann
___
Hot new product - Spider Networks introduces stunning online ePortfolio
solution for students and teachers
http://www.spider-networks.net/solutions/eportfolio.html
select A.name,A.district,x(GeomFromText(AsText(A.geo))) as
x,y(GeomFromText(AsText(A.geo))) as
y,(GLength(LineStringFromWKB(LineString(AsBinary(geo),AsBinary(GeomFromText('POINT(76.67472
11.83884)')) as Distance FROM (select geo,name,district from
cities_point union all select geo,name,district from cities_font_point
union all select geo,name,district from State_Highways_point union all
select geo,name,district from Other_Roads_point union all select
geo,name,district from Major_Roads_point union all select
geo,name,district from Vet_Clinics_point union all select
geo,name,district from University_point union all select
geo,name,district from Tourist_Info_point union all select
geo,name,district from Temples_point union all select geo,name,district
from Taxi_Stands_point union all select geo,name,district from
Stadiums_point union all select geo,name,district from
Sports_Clubs_point union all select geo,name,district from
Shops_WhiteGds_point union all select geo,name,district from
Shops_Sports_point union all select geo,name,district from
Shops_RealEstate_point union all select geo,name,district from
Shops_Music_point union all select geo,name,district from
Shops_Misc_point union all select geo,name,district from Shops_LPG_point
union all select geo,name,district from Shops_Jewellery_point union all
select geo,name,district from Shops_Furnt_point union all select
geo,name,district from Shops_Footwear_point union all select
geo,name,district from Shops_Computer_point union all select
geo,name,district from Shops_Chemists_point union all select
geo,name,district from Shops_Bakery_point union all select
geo,name,district from Shops_Apparel_point union all select
geo,name,district from Shopping_Ctrs_point union all select
geo,name,district from Services_Travel_point union all select
geo,name,district from Services_Professional_point union all select
geo,name,district from Services_Financial_point union all select
geo,name,district from Service_Stations_point union all select
geo,name,district from Schools_point union all select geo,name,district
from Restaurants_point union all select geo,name,district from
Religious_Pls_Oth_point union all select geo,name,district from
Railway_Stations_point union all select geo,name,district from
Railway_Reservations_point union all select geo,name,district from
PreSchools_point union all select geo,name,district from
PostOffices_point union all select geo,name,district from
PoliceStations_point union all select geo,name,district from
PoliceChaukis_point union all select geo,name,district from
PetrolPumps_point union all select geo,name,district from Parks_point
union all select geo,name,district from OtherInstt_point union all
select geo,name,district from Offices_point union all select
geo,name,district from Office_Airlines_point union all select
geo,name,district from Museums_point union all select geo,name,district
from Mosques_point union all select geo,name,district from Misc_point
union all select geo,name,district from Libraries_point union all select
geo,name,district from LevelCrossing_point union all select
geo,name,district from Industries_point union all select
geo,name,district from Hotels_point union all select geo,name,district
from Hostels_point union all select geo,name,district from
Hospitals_point union all select geo,name,district from
Historical_Pls_point union all select geo,name,district from
Gymnasiums_point union all select geo,name,district from Gurdwaras_point
union all select geo,name,district from Graveyards_point union all
select geo,name,district from GovtOffices_point union all select
geo,name,district from Dispensaries_point union all select
geo,name,district from Discotheques_point union all select
geo,name,district from Diagnostic_Ctrs_point union all select
geo,name,district from CyberCafes_point union all select
geo,name,district from Cultural_Centres_point union all select
geo,name,district from Crematory_point union all select
geo,name,district from Couriers_point union all select geo,name,district
from Computer_Instt_point union all select geo,name,district from
Colleges_point union all select geo,name,district from Clubs_point union
all select geo,name,district from Clinics_point union all select
geo,name,district from Cinemas_point union all select geo,name,district
from Church_point union all select geo,name,district from
BusTerminals_point union all select geo,name,district from
BusStops_point union all select geo,name,district from BusDepot_point
union all select geo,name,district from Buildings_point union all select
Re: can we convert shape files
yes. on linux it is possible to convert shp files you have to use libmygis avaliable at http://jcole.us/software/libmygis/ shann ___ Halloween Humour: Why did Dracula divorce his wife after 800 years? postmaster.co.uk http://www.postmaster.co.uk/cgi-bin/meme/quiz.pl?id=157 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM and InnoDB co-existance
hi, I need to have 2 DB in our development/production environment which require both InnoDB and MyISAM engines. This is bacasue, DB1 contains spatial/geom data which has to be on MyISAM engine and DB2 which needs transactional support has to be InnoDB. Now how to achieve this co-existance of both the MyISAM and InnoDB engines on the same server? regards shann ___ What is the largest island in the Mediterranean Sea? http:// -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM and InnoDB co-existance
hi, thnaks for the speedy reply. I already have both the DBs in my server. is there any way/method that the DB2 which needs transaction support can be converted from myisam to innodb format without any hitch regards shann ___ Which famous author was once captured by pirates and sold as a slave? http:// -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unable to start mysqld_multi
hi as per the online manual, i have configured mysql and able to run the server as mysqld_safe. but if i want to run mysqld_multi with the following configuration it doesnt work... the config file is like [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin user = mutli_admin password = admin_multi [mysqld2] socket = /tmp/mysql.sock2 port = 3307 pid-file = /usr/local/mysql/var2/hostname.pid2 datadir= /usr/local/mysql/var2 user = amitg [mysqld3] socket = /tmp/mysql.sock3 port = 3308 pid-file = /usr/local/mysql/var3/hostname.pid3 datadir= /usr/local/mysql/var3 user = shann i am starting the server with the following command /usr/local/mysql/bin/mysqld_multi start this command doesnt throw any errors as such and the report i am getting my executing the command /usr/local/mysql/bin/mysqld_multi report is Reporting MySQL servers MySQL server from group: mysqld2 is not running MySQL server from group: mysqld3 is not running what am i doing wrong. please guide me regards shann ___ Have your own email and web address for life. http://www.homemaster.net - Homemaster. Come Together. Online. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
write a spatial queary
hi, i have created / imported a table to mysql thru mysqlgisimport libraries and it has created a table with the following field / table structure Field name Type Allow nulls? Key Default value Extras id int(10) unsigned No Primary NULL auto_increment NAME varchar(100) No None TYPE varchar(100) No None SPEED_LIMI int(11) No None 0 ONE_WAYint(11) No None 0 geogeometry No Indexed now i need to write a sample query to demostrate the gis / spatial capabalities of mysql... i have gone thru all the web but didnt find an example for buffer(g,d) or Distance() functions.. please help out in writing these type of mysql queries thanks regards prashant ___ Share diaries and photos with your friends and family. http://www.homemaster.net - Homemaster. Come Together. Online. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
