Re: Problem with query using ST_Dwithin

On Jan 28, 4:32 pm, Nick <nick(dot)uebel(dot)(dot)(dot)(at)noaa(dot)gov> wrote:
> The following query's all work fine,
>
> select distinct zoa_metar_xml.stn_id, zoa_metar_xml.metar_txt,
> zoa_metar_xml.time, zoa_metar_xml.flt_cat, zoa_metar_xml.cld_cvr,
> zoa_metar_xml.cld_base, zoa_metar_xml.lonlat, zoa_metar_xml.geom from
> zoa_metar_xml;
>
> select distinct id, kml, type, min_hgt, max_hgt from
> zoa_sectors_basetop where zoa_sectors_basetop.id='$i' and
> (zoa_sectors_basetop.type = 'LO' or zoa_sectors_basetop.type='HILO');
>
> select distinct zoa_sectors_basetop.id, west_pirep_xml.raw_text,
> west_pirep_xml.cld_cvr, west_pirep_xml.cld_base,
> west_pirep_xml.cld_top, west_pirep_xml.altitude, west_pirep_xml.time,
> west_pirep_xml.acft_type, zoa_sectors_basetop.max_hgt,
> zoa_sectors_basetop.min_hgt, zoa_sectors_basetop.type,
> west_pirep_xml.lonlat from zoa_sectors_basetop, west_pirep_xml, artcc
> where (ST_DWithin(zoa_sectors_basetop.geom, west_pirep_xml.geom, 0)
> and ST_DWithin(artcc.the_geom, zoa_sectors_basetop.geom, 0)) and
> zoa_sectors_basetop.id='$i' and (west_pirep_xml.cld_base <= 5000 or
> west_pirep_xml.cld_top <= 10000) and west_pirep_xml.cld_cvr != 'SKC'
> and (zoa_sectors_basetop.type='LO' or
> zoa_sectors_basetop.type='HILO');
>
> So I'm not sure why this query is not working,
>
> select distinct zoa_metar_xml.stn_id, zoa_metar_xml.metar_txt,
> zoa_metar_xml.time, zoa_metar_xml.flt_cat, zoa_metar_xml.cld_cvr,
> zoa_metar_xml.cld_base, zoa_metar_xml.lonlat from zoa_metar_xml,
> zoa_sectors_basetop, artcc where  (ST_DWithin
> (zoa_sectors_basetop.geom, zoa_metar_xml.geom, 0) and ST_DWithin
> (artcc.the_geom, zoa_sectors_basetop.geom, 0)) and
> zoa_sectors_basetop.id='$i' and (zoa_sectors_basetop.type='LO' or
> zoa_sectors_basetop.type='HILO');
>
> Believe it has something to do with the ST_DWithin portion of the
> query (but these work in other queries)...all of these queries are
> being executed from a php file, then create kml files. The first query
> creates kml file for metars, this is working and geometry is there, so
> I'm not sure why I can't match these with the zoa_sectors_basetop.geom
> or artcc.the_geom, because the last working query also works when
> matching intersecting polygons with the zoa_sectors_basetop.geom. So
> looks like the problem may lie in the geometry in the table
> zoa_metar_xml. But can't figure why? Hope this is enough info, can
> anyone help? Thanks,
>
> Nick

Figured it out, just replaced the 0 with a .001, 0 must no longer work
in postgresql update.

(ST_DWithin(zoa_sectors_basetop.geom, zoa_metar_xml.geom, 0) and
ST_DWithin
(artcc.the_geom, zoa_sectors_basetop.geom, 0))