andrewniesen's questions

Eu sou um novato quando se trata de Postgres e percebo que perguntas semelhantes foram feitas antes, no entanto, estou lutando para fazer essa consulta funcionar:

SELECT    p_search.id, count(kia.image_id) AS count
FROM      (SELECT id,
                  keyword,
                  to_tsvector('english',keyword) AS tskw
          FROM keyword) AS p_search
LEFT JOIN keyword_image_assoc AS kia 
ON        kia.keyword_id = p_search.id
WHERE     p_search.tskw @@ plainto_tsquery('english','resident life')
OR        p_search.tskw @@ plainto_tsquery('english','wofford')
GROUP BY  kia.image_id
ORDER BY  count desc

Para contextualizar, tenho três tabelas:

• imageque tem um idcomo chave primária
• keywordque tem idcomo chave primária e keywordque é uma string representando uma palavra-chave
• keyword_image_assocque tem um id, image_ide keyword_idque é usado para associar imagens e palavras-chave canonicamente.

Se eu adicionar p_search.idà GROUP BYcláusula, os resultados não estão corretos - counté sempre 1. Existe uma maneira de agrupar os resultados para que os image_ids tenham a contagem do número de palavras-chave correspondentes?

Estou procurando duas colunas count- o número de palavras-chave correspondentes e image_ido id da imagem correspondente. Obrigado!

Estou tentando determinar o número total de colaboradores em um banco de dados de imagens que contribuíram com pelo menos cinco imagens. A consulta a seguir fornecerá contagens de imagens por colaborador, mas isso é apenas parte do quebra-cabeça. Tenho certeza que isso é simples para alguém que tem mais conhecimento do que eu :)

SELECT count(*) AS i_count,contributor_id FROM images GROUP BY contributor_id ORDER BY i_count DESC

Estou usando o Postgres e tenho uma tabela que contém metadados sobre vários milhões de imagens - muitas das quais têm o mesmo autor.

Estou procurando colaboradores que adicionam conteúdo regularmente - não pessoas que adicionam imagens em "rajadas" de muitas fotos em um curto período de tempo. Em outras palavras, prefiro pessoas que postam seis fotos em questão de meses do que 12 fotos em apenas uma semana, mas nunca postam fora desse curto espaço de tempo.

Escrever uma declaração como SELECT count(*) AS i_count,author_id FROM images GROUP BY author_id ORDER BY i_count DESCme dará uma lista em ordem decrescente de quem contribuiu com mais fotos para menos, mas como posso selecionar apenas pessoas que contribuíram com imagens em um período superior a 30 dias?

Eu também tenho uma date_addedcoluna.

Estou usando o Postgres com a seguinte consulta:

select count(*) from image;

A chave primária nesta tabela não é incrementada; é um número de série exclusivo para as imagens armazenadas na tabela. Nosso aplicativo geralmente tenta ingerir imagens que já foram gravadas no banco de dados, portanto, a chave primária/número de série garante que elas sejam gravadas apenas uma vez.

Agora estamos nos perguntando se deveríamos ter optado por uma chave primária incrementada. Temos 1.259.369 imagens no banco de dados e leva cerca de 7 minutos para a execução da consulta de contagem.

Nosso aplicativo nunca excluirá imagens desta tabela - portanto, uma chave primária incrementada nos permitiria verificar o valor do último ID que seria igual ao número de linhas na tabela.

Estou tentando instalar o Postgres 9.3 no Ubuntu 14.04 e estou recebendo um erro irritante na inicialização. Aqui está o que estou vendo:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server
* Error: could not exec /usr/lib/postgresql/9.3/bin/pg_ctl /usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main -l /var/log/postgresql/postgresql-9.3-main.log -s -o  -c config_file="/etc/postgresql/9.3/main/postgresql.conf" : [fail]

Então eu verifiquei o arquivo de log com esta saída:

 2015-01-05 21:50:05 EST LOG:  database system was shut down at 2015-01-05 21:50:03 EST
 2015-01-05 21:50:05 EST LOG:  database system is ready to accept connections
 2015-01-05 21:50:05 EST LOG:  autovacuum launcher started
 2015-01-05 21:50:06 EST LOG:  incomplete startup packet
 2015-01-05 21:51:22 EST ERROR:  syntax error at or near "exit" at character 1
 2015-01-05 21:51:22 EST STATEMENT:  exit;

"Pacote de inicialização incompleto" parece ser o culpado, mas estou tendo problemas para encontrar informações sobre o que pode estar acontecendo, então pensei em perguntar. Desde já, obrigado.

Estou tentando selecionar pontos dentro de polígonos usando as extensões GIS/espaciais para MySQL. Eu sou relativamente novo nisso, então tenha paciência comigo se esta for uma questão elementar.

Eu tenho pontos definidos como texto no banco de dados e estou tentando obter um valor booleano para saber se um ponto está dentro de um determinado polígono definido. Aqui estão minhas consultas, com uma WHEREinstrução lenta para garantir que todos retornem TRUE(observe que esses são valores LAT/LNG e o polígono é um quadrado começando no canto noroeste, sendo definido no sentido horário):

mysql> SET @zone = GeomFromText('Polygon((-73.9650952400 40.8072101100,-73.9531904800 40.8072101100,-73.9531904800 40.7982011000,-73.9650952400 40.7982011000,-73.9650952400 40.8072101100))');
Query OK, 0 rows affected (0.00 sec)

Verificando se esta consulta funcionou:

mysql> SELECT AsText(@zone);
+---------------------------------------------------------------------------------------------------------------------------------------+
| AsText(@zone)                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------------+
| POLYGON((-73.96509524 40.80721011,-73.95319048 40.80721011,-73.95319048 40.7982011,-73.96509524 40.7982011,-73.96509524 40.80721011)) |
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Em seguida, a declaração problemática:

mysql> SELECT id,latitude,longitude, MBRWithin(GeomFromText(geo),@zone), AsText(geo)
FROM image_meta WHERE 
(latitude BETWEEN 40.7982011 AND 40.80721011) AND
(longitude BETWEEN -73.96509524 AND -73.95319048)
LIMIT 10;

e o resultado:

+---------+---------------+----------------+------------------------------------+-----------------------------------+
| id      | latitude      | longitude      | MBRWithin(GeomFromText(geo),@zone) | AsText(geo)                       |
+---------+---------------+----------------+------------------------------------+-----------------------------------+
| 2598514 | 40.7982018590 | -73.9636609250 |                               NULL | POINT(-73.963660925 40.798201859) |
| 2490688 | 40.7982019370 | -73.9624817730 |                               NULL | POINT(-73.962481773 40.798201937) |
| 1325723 | 40.7982019940 | -73.9649998380 |                               NULL | POINT(-73.964999838 40.798201994) |
| 2466868 | 40.7982025000 | -73.9537353330 |                               NULL | POINT(-73.953735333 40.7982025)   |
|  442505 | 40.7982033330 | -73.9584200000 |                               NULL | POINT(-73.95842 40.798203333)     |
| 9517611 | 40.7982033330 | -73.9635783330 |                               NULL | POINT(-73.963578333 40.798203333) |
| 2681112 | 40.7982033580 | -73.9649211860 |                               NULL | POINT(-73.964921186 40.798203358) |
| 2681113 | 40.7982033580 | -73.9649211860 |                               NULL | POINT(-73.964921186 40.798203358) |
|  338950 | 40.7982050000 | -73.9649306570 |                               NULL | POINT(-73.964930657 40.798205)    |
| 1204589 | 40.7982052380 | -73.9649509480 |                               NULL | POINT(-73.964950948 40.798205238) |
+---------+---------------+----------------+------------------------------------+-----------------------------------+
10 rows in set (0.00 sec)

Esta consulta foi projetada com base nesta documentação: http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-mysql-specific.html#function_mbrcontains

Eu esperaria que a MBRWithin(GeomFromText(geo),@zone)coluna fosse TRUE(ou 1) para cada linha, mas eles são NULL- minha sintaxe está errada ou são minhas expectativas?

Muito obrigado antecipadamente.

Eu tenho um banco de dados com > 7,5 milhões de linhas (e crescendo), cada uma com um registro de imagem que possui uma geolocalização LAT/LNG específica representando onde a foto foi tirada, armazenada como valores DECIMAIS -

 mysql> describe image_meta;
 +---------------------+------------------+------+-----+-------------------+----------------+
 | Field               | Type             | Null | Key | Default           | Extra          |
 +---------------------+------------------+------+-----+-------------------+----------------+
 | id                  | int(11) unsigned | NO   | PRI | NULL              | auto_increment |
 | media_id            | varchar(255)     | YES  | UNI | NULL              |                |
 | user_id             | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_unix    | int(11) unsigned | YES  | MUL | NULL              |                |
 | create_time_zulu    | datetime         | YES  |     | NULL              |                |
 | latitude            | decimal(12,10)   | YES  | MUL | NULL              |                |
 | longitude           | decimal(13,10)   | YES  | MUL | NULL              |                |

Quero pesquisar dentro de uma área retangular, definida por LAT/LNG, e as consultas são muito lentas.

Estou usando esta consulta:

SELECT user_id FROM image_meta WHERE (latitude BETWEEN 40.779769 AND 40.792399 AND longitude BETWEEN -73.988457 AND -73.963308);

com o resultado:

123569 rows in set (8 min 28.99 sec)

Qual é a melhor maneira de fazer isso ir mais rápido? Devo pesquisar uma área mais ampla com menos algarismos significativos e, em seguida, usar o PHP para restringir os resultados pelos limites LAT/LNG mais precisos?

Sou um novato em SQL (sempre tive DBAs para me ajudar no passado), mas estou fazendo este projeto sozinho. Desde já, obrigado...

Alguém sabe por que isso está acontecendo?

 mysql> show tables;

 +-------------------+
 | Tables_in_photos  |
 +-------------------+
 | captions_images   |
 | image_meta        |
 | mentions_images   |
 | tags_images       |
 | words             |
 +-------------------+
 5 rows in set (0.00 sec)

 mysql> select * from captions_images;
 ERROR 1146 (42S02): Table 'ig_call.captions_images' doesn't exist
 mysql> 

Tentei consertar a mesa sem sucesso. O que poderia ajudar a corrigi-lo?

Estou criando um banco de dados que referencia imagens. Palavras-chave e legendas farão parte dos metadados. Essas legendas e palavras-chave não são necessariamente adicionadas às fotos com a intenção de facilitar buscas de qualidade.

Gostaria de converter efetivamente as informações nas legendas em palavras-chave. Obviamente, algumas das palavras nas legendas não serão úteis (por exemplo, "the","he","she","of",etc.) e eu as excluiria, mas outras palavras serão muito úteis, especialmente se forem aparecem em combinação com outras palavras.

Estou tentando descobrir a melhor maneira de associar essas palavras-chave e palavras das legendas às imagens. Seria melhor criar uma tabela de imagens (nomes de arquivo, caminho para arquivo, tamanho, etc.) e uma tabela de palavras, onde cada palavra tem um ID e, em seguida, uma tabela associativa que referencia as palavras às imagens? Ou talvez duas tabelas associativas, uma para vincular palavras de legenda e outra para vincular palavras-chave?

Será esta uma boa abordagem?

Tenho uma série de pontos de geolocalização em uma tabela e uma série de "zonas" em outra; Eu gostaria de fazer uma junção para identificar a qual zona cada ponto pertence (se houver).

A tabela de pontos fica assim:

+--+--------+---------+
|id|Latitude|Longitude|
+--+--------+---------+
| 1|37.23221|-81.32323|
+--+--------+---------+    
| 2|36.92321|-81.12423|
+--+--------+---------+
| etc                 |
+--+--------+---------+      

A tabela de zonas se parece com:

+--+------+------+------+-----+-----+
|id|Name  |LONG_E|LONG_W|LAT_N|LAT_S|
+--+------+------+------+-----+-----+
| 1|Zone 1| -81.0| -82.0| 38.0| 37.0|
+--+------+------+------+-----+-----+
| 2|Zone 2| -81.0| -82.0| 37.0| 36.0|
+--+------+------+------+-----+-----+
| etc                               |
+--+------+------+------+-----+-----+

Eu gostaria de poder consultar a tabela de pontos e ingressar na tabela de zonas, para obter um resultado parecido com:

+----+----------+-----------+----+------+
|p.id|p.latitude|p.longitude|z.id|z.name|
+----+----------+-----------+----+------+
|   1|  37.23221|  -81.32323|   1|Zone 1|
+----+----------+-----------+----|------+    
|   2|  36.92321|  -81.12423|   2|Zone 2|
+----+----------+-----------+----+------+
|etc                                    |
+----+----------+-----------+----+------+ 

Estou muito enferrujado em minha sintaxe SQL, então estou me perguntando se isso é possível... Estou usando o MySQL BTW. Obrigado!