I have table in Teradata Express that contains the countries of the world. The NAME column contains the country names and the GEOM colum contains their geographical information (the GEOM colum is of type ST_Geography). I created this table by importing a shapefile using Teradata's TDGeospatialImporter tool.
I am looking for a query that will return the list of countries that are contained in a particular boundingbox. So the boundingbox is the input and the GEOM column will be the output.
I came up with this query that does the job.
Select Name, GEOM from mstr.world where
GEOM.ST_MBR_Xmin() >= Xmin AND
GEOM.ST_MBR_Ymin() >= Ymin AND
GEOM.ST_MBR_Xmax() <= Xmax AND
GEOM.ST_MBR_Ymax() <= Ymax;
where obvioulsly (Xmin,Ymin,Xmax,Ymax) is the boundingbox.
However, this is just a test and in reality we will be querying tables that contain potentially millions or rows. So I guess the question is how can I be sure this is the most efficient query? Is this query using Teradata's spatial index? I have no idea. Is there a similar query that would be much faster?
Also, my technology department tells me that if the output (the GEOM colum in this case) can be in WKB format it would be much faster and better for us. I have tried using the ST_WKBToSQL method but I am not sure that's what I need. I guess what I need is a function that converts the ST_Geometry type to WKB format, so my query would be something like SELECT CONVERT(ST_GEOMETRY_TO_WKB, GEOM) from mstr.world blablabla
Can you give me a hand?
I guess there are two questions in the end, not just one ;)